Add fields dynamically to the expression

[madhubabum]

Hello Experts,

I am having one scenario,.

I am using Ad hoc report in Qliksense by using multiple dimensions and multiple expressions.

Base expression is : Sum(sales)/ Sum(Total Sales).

Here if i'm adding region has dimension, Expression should like  - sum(sales)/sum(Total<Region> Sales)

If i included one more dimension , expression should like - sum(sales)/sum(Total<Region,Area>sales)

Means, if i selected any dimension it is going to add or remove from denominator after total qualifier.

Any suggestions ?

Thanks in Advance.

Regards,

Madhu

[Anil_Babu_Samineni]

As you said, you are using Adhoc repot. for that, Perhaps this?

If(GetSelectedCount(Dimension)=0, Sum(sales)/ Sum(Total Sales),  Sum(sales)/ Sum(Total <$(=Concat(DISTINCT Dimension, ','))> Sales))

[madhubabum]

Hi Anil,

Thanks for your reply. But i am getting null values in the expression.

Regards,

Madhu

[Anil_Babu_Samineni]

Please replace the Dimension field with input field?

[madhubabum]

Anil - I want to add / remove fields dynamically from selected dimension values.

           With input filed i'm not able to get the expected result.(Measure is not going to calculate - Giving null values).

          is there any solution with Substringcount(Concat())  - ?

Thanks in Advance

[Anil_Babu_Samineni]

Should be possible

=SubStringCount('|' & $(=Concat(distinct Dimension, '|')) & '|', '|Measure1|')

TRy this in Text object and see?

$(=Concat(distinct Dimension, '|'))