Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
abc_18
Creator II
Creator II

how to get total sales based on drill down dimension selection

Hello Everyone,

I have a bar chart with 2 dimensions and 1 measure, as shown below:-

abc_18_0-1619434535469.png

 

Here one of the dimension State is a master dimension (a drill-down dimension).ro_so->ro_do->district

My requirement is to display the total sales value as a second measure which gives the total value grouping  for each product , for which I am using tooltip as shown below.

I tried using the expression :- sum(total <product> sales), but it's not giving me total value.

and if user select any sales office, then it should further drill down to District office and total sales value grouping should be based on ro_do product wise.

abc_18_1-1619505078434.png

Please suggest

Regards

 

 

12 Replies
JuanGerardo
Partner
Partner

Hi @abc_18 , you need to use the GetObjectDimension() function to get the dimension in the first position. More info here: https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Fiel...

JG

abc_18
Creator II
Creator II
Author

Hi juan,

Thanks for the response.

I tried using below expression :-

=Sum(TOTAL {$<Product=p(Product)>} quantity_sum), till level 3 it's working but at last level data is not summing up.

SO>DO->SA->RO, till SA level its working, I can see total sales value product wise, but if I select any SA it will further drill down to RO level, which is last one and in total sales I am getting blank value

JuanGerardo
Partner
Partner

If when selecting SA you go directly to RO level, this is because there is only one SA item, as when only one SA item available the dimension will jump to RO level (and doesn't matter if you select one SA or there is only one SA).

JG

abc_18
Creator II
Creator II
Author

Hi Juan,

For one sales area(SA) we have multiple RO's, at SA level for different SA's I am getting total sales, similarly if I select any SA, it will further drill down to RO's level and for each RO, we have some sales data.

But total sales at RO level is not showing

abc_18_0-1619595177654.png

Regards

JuanGerardo
Partner
Partner

Hi @abc_18, when I suggested to use GetObjectDimension() did you tried that option? I think it can work something like:

Sum(TOTAL <[$(=GetObjectDimension(0))]> quantity_sum)

JG

abc_18
Creator II
Creator II
Author

Hi Juan,

Yes, I tried with little modification:-

Sum(TOTAL {$<[$(=GetObjectDimension(0))],Product=p(Product)> }quantity_sum)

but this expression is also not giving total sales value at RO level (the last level)

 

Regards

JuanGerardo
Partner
Partner

I think you don't need set analysis, just the TOTAL sum. Have you tried with the expression I wrote before?

Sum(TOTAL <[$(=GetObjectDimension(0))]> quantity_sum)

abc_18
Creator II
Creator II
Author

Yes tried, but this gives me "error in expression".

If I add set analysis then only I am getting "Total Sales" till SA level.

JuanGerardo
Partner
Partner

Just in case, try to accept the expression even if you get an "error in expression". I got that error and ignore it as I think Qlik Sense is not able to parse this dollar expansion outside the set analysis.