topic how to get total sales based on drill down dimension selection in App Development

how to get total sales based on drill down dimension selection

abc_18 — Tue, 27 Apr 2021 06:55:05 GMT

Hello Everyone,

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

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.

Please suggest

Regards

Re: how to get total sales based on drill down dimension selection

JuanGerardo — Tue, 27 Apr 2021 07:11:18 GMT

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/FieldFunctions/getobjectdimension.htm

Re: how to get total sales based on drill down dimension selection

abc_18 — Tue, 27 Apr 2021 08:41:47 GMT

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

Re: how to get total sales based on drill down dimension selection

JuanGerardo — Tue, 27 Apr 2021 11:46:56 GMT

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).

Re: how to get total sales based on drill down dimension selection

abc_18 — Wed, 28 Apr 2021 07:36:12 GMT

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

Regards

Re: how to get total sales based on drill down dimension selection

JuanGerardo — Wed, 28 Apr 2021 08:24:59 GMT

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)

Re: how to get total sales based on drill down dimension selection

abc_18 — Wed, 28 Apr 2021 09:45:30 GMT

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

Re: how to get total sales based on drill down dimension selection

JuanGerardo — Wed, 28 Apr 2021 09:53:58 GMT

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)

Re: how to get total sales based on drill down dimension selection

abc_18 — Wed, 28 Apr 2021 11:07:22 GMT

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

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

Re: how to get total sales based on drill down dimension selection

JuanGerardo — Wed, 28 Apr 2021 13:11:33 GMT

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.

Re: how to get total sales based on drill down dimension selection

abc_18 — Wed, 28 Apr 2021 13:40:02 GMT

Hi Juan,

Thanks for your response. tried the above expression even with error, but same issue:-(

No total sales at RO level.

Regards

Re: how to get total sales based on drill down dimension selection

JuanGerardo — Wed, 28 Apr 2021 13:49:36 GMT

Then probably there is something in your data or model that has to be considered here. If you can replicate this with an example data set or app...

Re: how to get total sales based on drill down dimension selection

abc_18 — Fri, 30 Apr 2021 09:25:12 GMT

Hi Juan,

I tried working on the sample data and it's working perfectly fine there, but not in my application.

In my app I am using two tables by linking them, don't know what can be the issue. will again look into that.

Thanks for your help

Regards