Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Chart aggregation with Dimension missing Data

Hi all

I am trying to show a chart on each Salesperson's dashboard that will show the entire team's sales so they can compare how they are doing against the entire team.

Data:

IDSalespersonNameTeamItemRevenueYearMonth
001NickNorthPizza1520121
001NickNorthHotDog1020121
002KatieNorthPizza1220121
002KatieNorthPizza3020122
003HenryNorthPizza720121
003HenryNorthHotDog1020122
003HenryNorthHotDog1620121
004RyanWestPizza4020121
004RyanWestHotDog10120121
004RyanWestHotDog4520122

on the Pages for Nick and Henry, the graph displays correctly: 64 pizza vs 36 HotDog (Nick/Katie/Henry are all on the "North" team, so Ryan's sales are ignored)

correct.jpg

however the graph for Katie is incorrect, I am presuming because she only sold Pizza, but i want her graph to look like the above since she is still on the North team regardless of the fact that she sold 0 Hot Dog Individually.

bad.jpg

My chart is basaed on the "Item" dimension, expression is

=aggr(nodistinct sum({<Id=,SalespersonName=,Year={'2012'},Salesgroup=>}Revenue),[Item],[Salesgroup])

i think i am missing an if statement or something

thanks in advance!

1 Solution

Accepted Solutions
Not applicable
Author

I think i may have solved the issue..let me know if you see any problems with this formula, it seems to be working...

i added two expresions:

=num(aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam={'HotDog'},Salesgroup>}Revenue), Salesgroup)
/
aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam,Salesgroup>}Revenue), Salesgroup),'#,##0%')


=num(aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam={'Pizza'},Salesgroup>}Revenue), Salesgroup)
/
aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam,Salesgroup>}Revenue), Salesgroup),'#,##0%')


View solution in original post

3 Replies
swuehl
MVP
MVP

I haven't fully understood how your chart is build. You are using only one dimension (Item) and one expression (see above), but how do you get the stacked bar chart and how do you relate the chart to the salesperson on which dashboard the chart is located?

In general, I would assume that you don't need the aggr() function, a sum with TOTAL qualifier  and field list should do, maybe something like

sum(TOTAL<Item, Salesgroup> Revenue)

(this probably won't do, you might need to add the set expression to limit the data like you need etc, and I am not sure about the correct field list to TOTAL, since I don't understand your setting).

Regards,

Stefan

Not applicable
Author

sorry, forgot to mention in order to get the bar stacked like that I created a dummy calculated dimension called 'x' calculated as ='x' then just hid that off screen

Not applicable
Author

I think i may have solved the issue..let me know if you see any problems with this formula, it seems to be working...

i added two expresions:

=num(aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam={'HotDog'},Salesgroup>}Revenue), Salesgroup)
/
aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam,Salesgroup>}Revenue), Salesgroup),'#,##0%')


=num(aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam={'Pizza'},Salesgroup>}Revenue), Salesgroup)
/
aggr(sum({<Id=,SalespersonName=,Year={'2012'},Month={"<=$(vmaxmonth)"},Iteam,Salesgroup>}Revenue), Salesgroup),'#,##0%')