Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

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

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


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

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

Replies are listed 'Best First'.
Re^4: Unable to create a Pivot chart with visiable Columns
by chandantul (Scribe) on Sep 10, 2018 at 03:01 UTC

    You are a genius!! I have figured it out finally and resolved the issue after long time. Got a better understanding on the Chart. You are realyy helpful Thanks a lot!! My code below

    $strExcelFilename = "D:/PERL/" . "DEVICE-BROWSER-REPO +RT" . ".xlsx"; #$workbook1 = Spreadsheet::WriteExcel->new($strExcelFilename); $workbook1 = Excel::Writer::XLSX->new($strExcelFilename); #$worksheet1 = $workbook1->addworksheet("DEVICE-BROWSER-LIST") +; $worksheet1 = $workbook1->add_worksheet('Data'); #$worksheet2 = $workbook1->add_worksheet('Summary'); $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); #$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]); $app = $responseapps5[$k]; $device = $responseclientdevice[$k]; $browser = $responseclientbrowser[$k]; $userid = $responseid[$k]; ++$pivot{$app}{$device}; ++$pivot{$app}{$browser}; ++$categ{'device'}{$device}; ++$categ{'browser'}{$browser}; ++$app{$app}; $row1++; #print Dumper $app; } $worksheet2 = $workbook1->add_worksheet('Summary'); my @device = sort keys %{$categ{'device'}}; my @browser = sort keys %{$categ{'browser'}}; my @applist = sort keys %app; #print Dumper @applist; $worksheet2->write_row(0,1,\@applist); $row1 = 1; for my $categ (@device,@browser){ my $col = 0; $worksheet2->write($row1,$col++,$categ); for my $app (@applist){ $worksheet2->write($row1,$col++,$pivot{$app}{$categ}); } ++$row1 } my $chart = $workbook1->add_chart( type => 'column', embedded => 1 ); my $col = 'B'; for my $app (sort keys %app){ $chart->add_series( name => $app, categories => '=Summary!$A2:$A'.$row1, values => '=Summary!$'.$col.'$2:$'.$col.$row1, ); ++$col; } $chart->set_title ( name => 'Results of sample analysis' ); $chart->set_x_axis( name => 'Device/Browser' ); $chart->set_y_axis( name => 'User Count' ); $worksheet2->insert_chart( 'A'.($row1+2), $chart, 0, 0 ); $workbook1->close();
Re^4: Unable to create a Pivot chart with visiable Columns
by chandantul (Scribe) on Sep 10, 2018 at 16:36 UTC

    Hi, As per the Latest code result the table coming like this. Computer Mobile Tablet CHROME EDGE FIREFOX IE11 IE7 SAFARI UNKNOWN --- but I need Computer and Mobile as a Device and there row label will show how many time different browser was accessed for individual applications like before. Please check below example. Computer App 1 App 2 App3 Browser 1 2 3 11 Browser 2 Browser 3 Mobile Browser 1 3 4 12 Browser 2 Browser 3 Mobile Browser 1 4 5 15 Browser 2 Browser 3 This is the labeling I needed. Can you please help in my existing code that I shared with you? Please check below my current table code snippet

    $worksheet2 = $workbook1->add_worksheet('Summary'); my @device = sort keys %{$categ{'device'}}; my @browser = sort keys %{$categ{'browser'}}; my @applist = sort keys %app; #print Dumper @applist; $worksheet2->write_row(0,1,\@applist); $row1 = 1; for my $categ (@device,@browser){ my $col = 0; $worksheet2->write($row1,$col++,$categ); for my $app (@applist){ $worksheet2->write($row1,$col++,$pivot{$app}{$categ}); } ++$row1 }

      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
Re^4: Unable to create a Pivot chart with visiable Columns
by chandantul (Scribe) on Sep 10, 2018 at 18:42 UTC

    Thanks a lot Man!! I wanted to show like for all applications and the Computer , Mobile and other devices should be isolated from Browser names. Can we add this below in the Chart itself above Device/Browser but below browser's names in X axes? Grand Total 2 1 195 72 5 45 59 12 370 6 19 514 3 1303 Do I need to add the total count like below? Can you please help?

    for (sort keys %count){ $sheet2->write_row($row++,0,[$_,$count{$_}]); }

      I'm sorry I don't understand your requirement. Do you mean adding a line on the chart like this ?

      
       5 |      y
       4 |  y   y  x            x app1
       3 |  y  xy  x        y   y app2
       2 |  yz xy  xy   y   yz  z app3
       1 | xyz xyz xyz xyz xyz
         ------------------------------
           com lap tab br1 br2 
            insert line here ?
               Device/Browser
      
      poj

        Thanks for your responses! As per your example, com lap tab br1 br2 should be showing as browsers. The sections for different devices will be looking like below and just above Chart in Chart Table at last line after counts there would be total counts of hits for different applications by different browsers as GRAND TOTAL without breaking earlier codes . Like below. Grand Total 5 | y 4 | y y x x app1 3 | y xy x y y app2 2 | yz xy xy y yz z app3 1 | xyz xyz xyz xyz xyz -------------------------------------------------------------------- br1 br2 br3 br4 etc br1 br2 br3 br4 etc br1 br2 br3 br4 etc Computer Tablet Mobile Device/Browser

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2024-03-29 15:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found