Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
miranrai
Contributor III
Contributor III

Custom expression

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.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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))),

  Sum(EXPOSURE)

  )

PFA

View solution in original post

12 Replies
Anil_Babu_Samineni

I am not sure, I followed you. Is this you are expecting?

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
miranrai
Contributor III
Contributor III
Author

yes ... exactly

miranrai
Contributor III
Contributor III
Author

Is this possible in qlikview?

tresesco
MVP
MVP

PFA

Anil_Babu_Samineni

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
miranrai
Contributor III
Contributor III
Author

Thank you.. can we do it on expression?

miranrai
Contributor III
Contributor III
Author

Anil, i dont want to format red color. I want sum of largest numbers of X & Y for Z value.

ganeshsvm
Creator II
Creator II

Hi Mira,

I have tried out you requirement in script, try out the script below:

SampleDataRaw:
LOAD *,
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,
]
;


Join
SampleData:
LOAD *,
if(Right(Key,2) = Right(Peek(Key),2), RangeSum(Peek(MaxExp), Peek(Exposum))) as Exposum;
LOAD
PN_Status&Country as Key,
Max(Exposure) as MaxExp 
Resident SampleDataRaw
Group By
PN_Status&Country;

Final:
LOAD *,
if(PN_Status = 'Z', Exposum, Exposure) as ExpoNew
Resident SampleDataRaw;


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.

-Ganesh

Anil_Babu_Samineni

That's for it deserve. I've mentioned same thing over above reply. Can you show the image of object how it seems

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)