Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Unable to create a Pivot chart with visiable Columns

by chandantul (Scribe)
on Sep 07, 2018 at 20:58 UTC ( #1221937=perlquestion: print w/replies, xml ) Need Help??

chandantul has asked for the wisdom of the Perl Monks concerning the following question:

Hello Team, I have been trying to show the Column chart by linking one sheet on the same file but I was unable to add 2 columns categories under single legend. I need your help to fix this issue. Please check my Code snippet below

$workbook1 = Excel::Writer::XLSX->new($strExcelFilename); #$worksheet1 = $workbook1->addworksheet("DEVICE-BROWSER-LIST") +; $worksheet1 = $workbook1->add_worksheet('Data'); $worksheet2 = $workbook1->add_worksheet('Summary'); $row1= 1; foreach $worksheet1 ($workbook1->sheets()) { if( $worksheet1->get_name() eq $SUCCESS_COUNT_SHEET_NAME ) { $format = $workbook1->add_format(%header); $worksheet1->write(0,0, "User Id", $format); $worksheet1->write(0,1, "User Display Name", $format); $worksheet1->write(0,2, "User Email Id", $format); $worksheet1->write(0,3, "Device", $format); $worksheet1->write(0,4, "Browser", $format); $worksheet1->write(0,5,"Application Name", $format); $worksheet1->write(0,6,"TimeStamp", $format); #$worksheet1->write(0,7, "EMAIL--ITUSER-STATUS", $format); for ($k = 0; $k <= $#responseid; $k++) { $worksheet1->write($row1,0, $responseid[$k]); $worksheet1->write($row1,1, $responsdisp[$k]); $worksheet1->write($row1,2, $responsalter[$k]); $worksheet1->write($row1,3, $responseclientdevice[$k]); $worksheet1->write($row1,4, $responseclientbrowser[$k]); $worksheet1->write($row1,5, $responseapps5[$k]); $worksheet1->write($row1,6, $estdate[$k]); $row1++; } } $chart1 = $workbook1->add_chart( type => 'column', embedded => 1 + ); $chart1->add_series( categories => '='. "Data" . '!$E$2:$E$1200', name => '='."Data".'!$F$1', values => '='."Data".'!$F$2:$F$1200', ); $chart1->add_series( categories => '='. "Data" . '!$D$2:$E$1200', values => '='."Data".'!$D$2:$D$1200', ); $chart1->set_title ( name => 'Results of sample analysis +' ); $chart1->set_x_axis( name => 'Computer' ); $chart1->set_y_axis( name => 'Browser' ); $chart1->set_style( 11 ); $worksheet2->insert_chart( 'E2', $chart1, 25, 10 ); } $workbook1->close();

Replies are listed 'Best First'.
Re: Unable to create a Pivot chart with visiable Columns
by poj (Abbot) on Sep 08, 2018 at 10:38 UTC

    I'm not clear what you are trying to do but to create a PivotChart you first need to create a pivot table. Are you trying to create a bar chart of counts for browser types like this ?

    #!/usr/bin/perl use strict; use Excel::Writer::XLSX; my $strExcelFilename= 'chart.xlsx'; my $book = Excel::Writer::XLSX->new($strExcelFilename); my $format = $book->add_format(); my $sheet1 = $book->add_worksheet('Data'); my @header = ('User ID','Browser'); for my $col(0..$#header){ $sheet1->write(0,$col, $header[$col], $format); } my $row = 1; my %count=(); # browser count while (<DATA>) { chomp; next unless /\S/; my @f = split ';',$_; ++$count{$f[1]}; $sheet1->write_row($row++,0, \@f); } # add summary chart my $sheet2 = $book->add_worksheet('Summary'); $sheet2->write_row(0,0,['Browser','Count']); $row = 1; for (sort keys %count){ $sheet2->write_row($row++,0,[$_,$count{$_}]); } my $chart = $book->add_chart( type => 'column', embedded => 1 ); $chart->add_series( name => 'Count', categories => '=Summary!$A$2:$A$'.$row, values => '=Summary!$B$2:$B$'.$row, ); $chart->set_title ( name => 'Results of sample analysis' ); $chart->set_x_axis( name => 'Browser' ); $chart->set_y_axis( name => 'Count of Computer' ); $chart->set_style( 11 ); # add chart $sheet2->insert_chart( 'E2', $chart, 25, 10 ); $book->close(); __DATA__ user1;Chrome user2;IE11 user3;IE11 user4;Chrome user5;Firefox user6;IE11 user7;Safari

      Thanks for your kind responses! I would like to create a Column chart first that will consists of Application Names as legends and Browser and Device columns should be in Categories. Application Names are 10 types in my data but there are 1200 entries for different users at different times of accesses hence I would like to count of users in OKTA ID columns ( 1200 ) and check how many times different applications was accessed by different browsers ( Chrome, IE11, Firefox or Safari) and Devices ( Computer or Mobile). Hope I was able to make you understand my intention. I was unable to figure it as I am bit new in Excel Writer Chart and I am using Windows 7 64 bits . Please help if possible.

        Ok, see if this is closer

        #!/usr/bin/perl use strict; use Excel::Writer::XLSX; my $strExcelFilename= 'chart.xlsx'; my $book = Excel::Writer::XLSX->new($strExcelFilename); my $format = $book->add_format(); my $sheet1 = $book->add_worksheet('Data'); my @header = ('User ID','Device','Browser','Application'); for my $col(0..$#header){ $sheet1->write(0,$col, $header[$col], $format); } my $row = 1; my %pivot=(); # pivot table count my %categ=(); my %app =(); while (<DATA>) { chomp; next unless /\S/; my @f = split ';',$_; my ($userid,$device,$browser,$app) = @f; ++$pivot{$app}{$device}; ++$pivot{$app}{$browser}; ++$categ{'device'}{$device}; ++$categ{'browser'}{$browser}; ++$app{$app}; $sheet1->write_row($row++,0, \@f); } # create summary pivot table my $sheet2 = $book->add_worksheet('Summary'); my @device = sort keys %{$categ{'device'}}; my @browser = sort keys %{$categ{'browser'}}; my @applist = sort keys %app; $sheet2->write_row(0,1,\@applist); $row = 1; for my $categ (@device,@browser){ my $col = 0; $sheet2->write($row,$col++,$categ); for my $app (@applist){ $sheet2->write($row,$col++,$pivot{$app}{$categ}); } ++$row } my $chart = $book->add_chart( type => 'column', embedded => 1 ); my $col = 'B'; for my $app (sort keys %app){ $chart->add_series( name => $app, categories => '=Summary!$A2:$A'.$row, values => '=Summary!$'.$col.'$2:$'.$col.$row, ); ++$col; } $chart->set_title ( name => 'Results of sample analysis' ); $chart->set_x_axis( name => 'Device/Browser' ); $chart->set_y_axis( name => 'User Count' ); #$chart->set_style( 11 ); # add chart $sheet2->insert_chart( 'A'.($row+2), $chart, 0, 0 ); $book->close(); # user;device;browser;app __DATA__ user1;laptop;Chrome;AppA user2;laptop;IE11;AppB user3;desktop;IE11;AppC user4;laptop;Chrome;AppD user5;laptop;Firefox;AppA user6;laptop;IE11;AppC user7;desktop;Safari;AppE user8;desktop;Safari;AppE user9;desktop;Safari;AppE
        poj

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (4)
As of 2022-05-28 16:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (99 votes). Check out past polls.

    Notices?