in reply to Re^12: Unable to create a Pivot chart with visiable Columns
in thread Unable to create a Pivot chart with visiable Columns
Thanks for your responses Sir! Please check below Code snippet and kindly let me know if I need to replace some block of codes against the exiting code. This is very much required to isolate Devices keys and browsers keys in the chart and the requirement was for to keep the Devices keys like Computer - Mobile - Tablet below of the browsers names in the Chart like below browser1 Browser2 Browser3 Browser1 browser2 Browser3 Browser1 Browser2 Browser3 Computer Mobile Tablet Device/Browser
$strExcelFilename = "D:/PERL/" . "DEVICE-BROWS +ER-REPORT" . ".xlsx"; $workbook1 = Excel::Writer::XLSX->new($strExcelFilename); my $format_bold = $workbook1->add_format( bold => 1, top=>1, b +ottom=>1 ); $worksheet1 = $workbook1->add_worksheet('Data'); $row1= 1; #$chart = $workbook1->add_chart( type => 'column', embedded => + 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); } } 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]); $app = $responseapps5[$k]; $device = $responseclientdevice[$k]; $browser = $responseclientbrowser[$k]; $userid = $responseid[$k]; ++$pivot{$app}{$device}{$browser}; ++$pivot{$app}{'TOTAL'}{$browser}; ++$pivot{$app}{'TOTAL'}{'TOTAL'}; ++$pivot{$app}{$device}{'TOTAL'}; ++$pivot{'TOTAL'}{$device}{$browser}; #++$pivot{$app}{$browser}; ++$categ{'device'}{$device}; ++$categ{'browser'}{$browser}; ++$app{$app}; $row1++; #print Dumper $app; } $worksheet2 = $workbook1->add_worksheet('Summary'); $worksheet2->set_column(0,0,20); my @device = sort keys %{$categ{'device'}}; my @browser = sort keys %{$categ{'browser'}}; my @applist = sort keys %app; my $row2 = 0; $worksheet2->write($row2,1,\@applist); for my $device (@device){ my $col2 = 0; $worksheet2->write(++$row2,$col2,$device,$format_bold); for my $app (@applist){ $worksheet2->write($row2,++$col2,$pivot{$app}{$device}{'TOTAL'},$ +format_bold); } for my $bro (@browser){ $col2 = 0; $worksheet2->write(++$row2,$col2,substr($device,0,3).'-'.$bro); + for my $app (@applist){ $worksheet2->write($row2,++$col2,$pivot{$app}{$device}{$bro}); + } } ++$row2; } # GRAND TOTAL my $col2 = 0; $worksheet2->write(++$row2,$col2,'GRAND TOTAL',$format_bold); for my $app (@applist){ $worksheet2->write($row2,++$col2,$pivot{$app}{'TOTAL'}{'TOTAL'},$for +mat_bold); } my $chart = $workbook1->add_chart( type => 'column', embedded => 1 ); $chart->set_size( width => 1000, height => 500 ); #my $col = 'B'; $col2 = 0; for my $app (sort keys %app){ ++$col2; $chart->add_series( name => $app, categories => ['Summary',1,$row2,0,0], values => ['Summary',1,$row2,$col2,$col2] ); } $chart->set_title ( name => 'Results of sample analysis' ); $chart->set_x_axis( name => 'Device/Browser' ); $chart->set_y_axis( name => 'User Count' ); # add chart $worksheet2->insert_chart( 22, 1 , $chart); $workbook1->close();
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re^14: Unable to create a Pivot chart with visiable Columns
by poj (Abbot) on Sep 12, 2018 at 15:33 UTC | |
by chandantul (Scribe) on Sep 12, 2018 at 16:15 UTC | |
by poj (Abbot) on Sep 12, 2018 at 16:28 UTC | |
by chandantul (Scribe) on Sep 12, 2018 at 17:31 UTC | |
by poj (Abbot) on Sep 12, 2018 at 17:47 UTC | |
|