Dynamically include or Exclude Dimensions after TOTAL qualifier

madhubabum — Sat, 16 Nov 2024 20:01:11 GMT

Hi All,

I am having the below base expression : Sum(Sales)/Sum(TOTAL Sales)

If i select 'Region' Dimension from Adhoc Dimension list : Expression like : Sum(Sales)/Sum(TOTAL <Region>Sales)

If i select one more Dimension 'Area' from Adhoc Dimension list : Expression like : Sum(Sales)/Sum(TOTAL <Region,Area>Sales)

Note : If i selected one more dimension like 'City', we need to exclude form expression after TOTAL qualifier.

Means always i need to exclude City dimension from expression after TOTAL qualifier.

Any suggestions ?

Thanks in Advance

Re: Dynamically include or Exclude Dimensions after TOTAL qualifier

marcus_sommer — Wed, 25 Sep 2019 07:33:07 GMT

I think I would just add another dimension-list which controls the TOTAL and querying it within the expressions, like:

sum($(=if(getselectedcount(TotalList)=0, '', 'TOTAL <' & concat(TotalList, ',') & '>')) Sales)

To keep the expressions more readable you may outsource the adhoc-variable into a normal variable.

- Marcus