Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re^5: Unable to create a Pivot chart with visiable Columns

by poj (Abbot)
on Sep 10, 2018 at 17:48 UTC ( [id://1222075]=note: print w/replies, xml ) Need Help??


in reply to Re^4: Unable to create a Pivot chart with visiable Columns
in thread Unable to create a Pivot chart with visiable Columns

You need to change the structure to $pivot{$app}{$device}{$browser}

#!/usr/bin/perl use strict; use warnings; use Excel::Writer::XLSX; # set up with test data my @responseid; my @responsdisp; my @responsalter; my @responseclientdevice; my @responseclientbrowser; my @responseapps5; my @estdate; test_data(); my $strExcelFilename= 'chart.xlsx'; my $book = Excel::Writer::XLSX->new($strExcelFilename); my $format = $book->add_format(); my $worksheet1 = $book->add_worksheet('Data'); my @header = ('User Id','User Display Name','User Email Id','Device', 'Browser','Application Name','Timestamp'); $worksheet1->write(0,0, \@header, $format); my $row1 = 1; my %pivot=(); # pivot table count my %categ=(); my %app =(); for my $k (0..$#responseid) { my @f = ( $responseid[$k], #0 $responsdisp[$k], #1 $responsalter[$k],#2 $responseclientdevice[$k], #3 $responseclientbrowser[$k],#4 $responseapps5[$k], #5 $estdate[$k], #6 ); $worksheet1->write($row1,0, \@f); my $device = $f[3]; my $browser = $f[4]; my $app = $f[5]; ++$pivot{$app}{$device}{$browser}; ++$categ{'device'}{$device}; ++$categ{'browser'}{$browser}; ++$app{$app}; $row1++; #print Dumper $app; } # create summary pivot table my $worksheet2 = $book->add_worksheet('Summary'); my @device = sort keys %{$categ{'device'}}; my @browser = sort keys %{$categ{'browser'}}; my @applist = sort keys %app; $row1 = 0; for my $device (@device){ $worksheet2->write($row1++,0,[$device,@applist]); for my $browser (@browser){ my $col = 0; $worksheet2->write($row1,0,$browser); for my $app (@applist){ $worksheet2->write($row1,++$col,$pivot{$app}{$device}{$browser}) +; } ++$row1; } ++$row1; } sub test_data { while (<DATA>) { chomp; next unless /\S/; my ($userid,$device,$browser,$app) = split ';',$_; push @responseid,$userid; push @responseclientdevice,$device; push @responseclientbrowser,$browser; push @responseapps5,$app; } } __DATA__ user1;Mobile;Chrome;AppA user2;Mobile;IE11;AppB user3;Computer;IE11;AppC user4;Mobile;Chrome;AppD user5;Computer;Firefox;AppA user6;Tablet;IE11;AppC user7;Tablet;Safari;AppE user8;Computer;Safari;AppE user9;Computer;Safari;AppE
poj

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1222075]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2024-03-28 22:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found