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

[Chanty4u]

may be try this

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

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

[sasiparupudi1]

Try

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

[trdandamudi]

Not getting any values..

Thanks

[MK_QSL]

What is vScore definition?

[Chanty4u]

try this

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

[trdandamudi]

Manish,

The vScore-1 definition is as below: ( I created them in the variable window)

Score-1 <--- Variable

If($(vHI) >= Ta_In_Hu,10,Num(($(vHI) - Min_HI)/(Ta_In_Hu - Min_HI) * 20,'##'))  <--- Expression

vHI <--- variable

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

While the vScore-1 variable is evaluating the value it also looks into the variable "vHI" as mentioned above. Hope this helps..

Thanks

[MK_QSL]

You need to use along side

[HI]={'Y'}

But again question is what is vHchy_Select_Dim?

[trdandamudi]

vHchy_Select_Dim is variable defined as below:

vHchy_Select_Dim <--- Variable

=('['&_Work_Dims&']')   <--- Expression ( This is getting the data from an data island table as show below)

LOAD * INLINE [
    _Work_Dims
     Level A View
     Level 1
     Level 2
     Level 3
     Level 4
     Cc_Level 1
     Cc_Level 2
     Cc_Level 3
     Cc_Level 4
     Cc_Level 5

];

With the existing expressions if I select a month in the list box then every month shows the correct values. I want to show those months in one pivot like Jan, Feb Mar etc. ( That is the reason I want to pass the month in the Set

analysis)

Thanks

];

[stevedark]

The issue is that the set analysis needs to go inside the aggregations, and the num function is not an aggregation.

You will need to insert the set analysis into both of the variables (which I presume do have aggregation functions in them).

If you really don't want to do that then this should work, but won't be as efficient as it could be:

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