I have a requirement where i need to calculate based on dimension values in expression.
I have tried to use aggr function but still can't get result what i wanted. I am not sure where i am going wrong.
What i need is, say for AU for PN_Status = 'Z' , value should be sum max values for both X & Y. i.e. , Z = 50.
In attached screen shot
Appreciate your help.
Solved! Go to Solution.
For front end solution, check this:
Expression to be used:
If ( PN_STATUS='Z', RangeSum(max( total <COUNTRY> If(PN_STATUS='X',EXPOSURE)), max(total <COUNTRY> If(PN_STATUS='Y',EXPOSURE))),
I am not sure, I followed you. Is this you are expecting?
Simple, Take 3 Dimensions and use Sum(EXPOSURE) and Total Mode should me Expression Total
Then, Give Text Color -- If(PN_STATUS = 'Z', LightRed())
and Use Same as Bold for that, Then you can achieve
This should work either Pivot / Straight objects?
I have tried out you requirement in script, try out the script below:
PN_Status&Country as Key;
LOAD * Inline [
Country, Region, PN_Status, Exposure
AU, Asia Region, X, 10
AU, Asia Region, X, 30
AU, Asia Region, X, 20
AU, Asia Region, Y, 10
AU, Asia Region, Y, 30
AU, Asia Region, Z,
NZ, NZ Region, X, 15
NZ, NZ Region, X, 25
NZ, NZ Region, Y, 35
NZ, NZ Region, Y, 25
NZ, NZ Region, Y, 45
NZ, NZ Region, Z,
if(Right(Key,2) = Right(Peek(Key),2), RangeSum(Peek(MaxExp), Peek(Exposum))) as Exposum;
PN_Status&Country as Key,
Max(Exposure) as MaxExp
if(PN_Status = 'Z', Exposum, Exposure) as ExpoNew
DROP Fields MaxExp,Exposure,Key, Exposum;
DROP Table SampleDataRaw;
Hope this helps and also look at the solutions provided by the experts which may be more optimized than mine.
That's for it deserve. I've mentioned same thing over above reply. Can you show the image of object how it seems