Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
may be try this
=Num({ 1 <Month={'Jan'}>} '$(vScore-1)'+'$(vScore-2)', '##.0')
=Num({ 1 <Month={'Jan'}>} "=$(vScore-1)"+"=$(vScore-2)", '##.0')
Try
=Num(Only({ 1<Month={'Jan'}>} $(vScore-1)+$(vScore-2)), '##.0')
Not getting any values..
Thanks
What is vScore definition?
try this
=Num({ 1 <$(vScore-1)+$(vScore-2),Month={'Jan'}>} , '##.0')
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
You need to use along side
[HI]={'Y'}
But again question is what is vHchy_Select_Dim?
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
];
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')