topic Re: Custom expression in QlikView

Custom expression

miranrai — Mon, 27 Feb 2017 08:08:57 GMT

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.

Re: Custom expression

Anil_Babu_Samineni — Mon, 27 Feb 2017 08:29:20 GMT

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

Re: Custom expression

miranrai — Mon, 27 Feb 2017 08:59:47 GMT

yes ... exactly

Re: Custom expression

miranrai — Mon, 27 Feb 2017 10:50:45 GMT

Is this possible in qlikview?

Re: Custom expression

tresB — Mon, 27 Feb 2017 12:22:16 GMT

PFA

Re: Custom expression

Anil_Babu_Samineni — Mon, 27 Feb 2017 12:43:32 GMT

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?

Re: Custom expression

miranrai — Wed, 01 Mar 2017 05:58:39 GMT

Thank you.. can we do it on expression?

Re: Custom expression

miranrai — Wed, 01 Mar 2017 06:00:00 GMT

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

Re: Custom expression

ganeshsvm — Wed, 01 Mar 2017 06:02:01 GMT

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

Re: Custom expression

Anil_Babu_Samineni — Wed, 01 Mar 2017 06:45:59 GMT

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

Re: Custom expression

tresB — Wed, 01 Mar 2017 06:54:20 GMT

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

Re: Custom expression

Anil_Babu_Samineni — Wed, 01 Mar 2017 07:03:45 GMT

Or This miranrai

I've done simple add Dimension called EXPOSURE with short Expression Sum(EXPOSURE)

Re: Custom expression

miranrai — Wed, 01 Mar 2017 08:42:43 GMT

Thank you Tresesco