How to use set analysis while calling a variable

[trdandamudi]

Hi,

In my Pivot table I am using one calculated dimension and an expression as below:

=Num($(vScore-1) + $(vScore-2), '##.0')        <-- My current selections in list box for Month='Feb'

and I am getting the correct results.

Now, I want to add one more expression where it gets data for the month='Jan' like below

=Num({ 1 <Month={'Jan'}>} $(vScore-1)+$(vScore-2), '##.0')     <-- This expression is not working and I am getting 0

How can I re wite the above expression ?

Thanks

[trdandamudi]

Steve,

I applied the logic from the below line into my code:

=Num(sum({1<Month={'Jan'}>} aggr($(vScore-1)+$(vScore-2), Month), '##.0')

In my dimension I have 11 different values and when I applied above expression it is giving value for only one row and remaining are zeros ? what ever the value it is displaying for one row is correct. am I missing something here ?

Thanks for your time.

[stevedark · Accepted solution]

Sorry, I missed out some rather important code. 

The AGGR statement allows you to calculate values for each of the dimensions you specify in the paramters and then do a further aggregation (and set analysis) over them.  This is why Month was added and the correct answer was returned.

You also need to add all of the other dimensions that you have in your table to the parameter list, e.g.

=Num(sum({1<Month={'Jan'}>} aggr($(vScore-1)+$(vScore-2), Month,Field1,Field2), '##.0')

I suspect you will need to add $(=vHchy_Select_Dim) as one of the fields.

[trdandamudi]

I re did the expression based on the below format:

=Num(sum({1<Month={'Jan'}>} aggr(aggr($(vScore-1)+$(vScore-2), Month,$(vHchy_Select_Dim)),$(vHchy_Select_Dim)) '##.0')

I am getting the results for all the rows but it is not applying the Set analysis for "Month" and  the sub total is wrong ?

Thanks

[stevedark]

$(vHchy_Select_Dim) should only appear once.

You also need to include any other fields which are included as dimensions, or does the table only have the one dimension?

Is Month the name of a field in your data model?  Does it contain the names of the months, exactly as you are matching them in the set analysis?

Having the set analysis inside the two expressions (rather than outside with an aggr) would definitely be better.  It would need to go in your vHI variable by the looks of things.  This will require more duplication of variables, but would be ultimately better.

You could look at this article about passing parameters into variables - but this could be overkill for your use case:

https://www.quickintelligence.co.uk/variables-parameters-load-script/

[trdandamudi]

I have only one calculated dimension and yes "Month" is a field in the data model. Looks like I need to add more variables. But one set of variables do not have any aggregation and they all have only Num().

Thanks

[sasiparupudi1]

Try adding your set analysis to the aggregation

In the inside variable.

vHI <--- variable

Num(Sum({<[HI]={'Y'},Month={'Jan'}>} _SS_Count)/Sum({<[HI]={'Y'},[D-LS-MA]= Month={'Jan'} >} Total<$(=vHchy_Select_Dim)> _SS_Count),'###%')   <--- Expression

[stevedark]

This code is missing a comma before Month.  Also, care needs to be taken as that variable is used for other periods as well.

[sunny_talwar]

Why don't you share a sample (mocked up or anonymized) to help us see your issue