how to get total sales based on drill down dimension selection

[abc_18]

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

[JuanGerardo]

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]

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]

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]

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

[JuanGerardo]

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]

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]

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]

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]

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.