Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
oscarmqz
Creator III
Creator III

aggr in pivot table not showing in all rows

Hi, I have the below example, in first table I have everything, but I need to show it like in the second one, only by SLS_MTRC_ID,

to count the total number of sales_territories I'm using this formula: aggr(count(SALES_TERRITORY_ID),SALES_EMPLOYEE_ID), which is giving me the correct figures 4 and 9, but why do they show up only for the first sales_metric dimension row of each employee?

Same for total Q by rep: aggr(sum(QUOTA_SALES_METRIC),SALES_EMPLOYEE_ID)

Capture.PNG.png

1 Solution

Accepted Solutions
muniyandi
Creator III
Creator III

Hi Oscar,

let's Try below expression:


aggr(  Nodistinct count(SALES_TERRITORY_ID),SALES_EMPLOYEE_ID),

aggr( Nodistinct sum(QUOTA_SALES_METRIC),SALES_EMPLOYEE_ID)


Post your data in excel file.


Thanks,

Muni

View solution in original post

3 Replies
jaimeaguilar
Partner
Partner

Hi,

When using aggr you need to take care about the involved dimensions in your chart.

In the case of your example for the second chart you're creating a chart with SLS_MTRC_ID as dimension. then your formula should be something like this (it's what I can figure out just by seeing the screenshots):

aggr(count(SALES_TERRITORY_ID),SALES_EMPLOYEE_ID, SLS_MTRC_ID)


Also, as tip try not to use key fields directly in expressions. It is recommended to create a duplicate of these fields if you want to include fields that are link between different tables in charts,


regards

muniyandi
Creator III
Creator III

Hi Oscar,

let's Try below expression:


aggr(  Nodistinct count(SALES_TERRITORY_ID),SALES_EMPLOYEE_ID),

aggr( Nodistinct sum(QUOTA_SALES_METRIC),SALES_EMPLOYEE_ID)


Post your data in excel file.


Thanks,

Muni

View solution in original post

oscarmqz
Creator III
Creator III
Author

Great! that was it, thank you very much