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