10 Replies Latest reply: Jan 3, 2017 8:16 AM by Sunny Talwar

# variable in set analysis P() and E()

Hi,

I´ve tried two functions as a variable:

if(GetSelectedCount(OTDStage)>1, '',

if(GetSelectedCount(OTDStage)=0, '',

if(GetFieldSelections(OTDStage) = '1. Earlier -5', '1. Earlier -5',

if(GetFieldSelections(OTDStage) = '2. On Time -3 / 0 Days', '2. On Time -3 / 0 Days',

if(GetFieldSelections(OTDStage) = '3. On Time -3 / + 1 Day' , '3. On Time -3 / + 1 Day',

if(GetFieldSelections(OTDStage) = '4. On Time -3 / + 2 Days', '4. On Time -3 / + 2 Days',

if(GetFieldSelections(OTDStage) = '5. On Time -5 / 0 Days', '5. On Time -5 / 0 Days',

if(GetFieldSelections(OTDStage) = '6. On Time -5 / + 1 Day', '6. On Time -5 / + 1 Day',

''))))))))

Result example in Qlik View text box: 1. Earlier -5

or

=if(GetSelectedCount(OTDStage)>1, '',

if(GetSelectedCount(OTDStage)=0, '',

if(GetFieldSelections(OTDStage) = '1. Earlier -5', chr(39) & '1. Earlier -5' & chr(39),

if(GetFieldSelections(OTDStage) = '2. On Time -3 / 0 Days', chr(39) & '2. On Time -3 / 0 Days' & chr(39),

if(GetFieldSelections(OTDStage) = '3. On Time -3 / + 1 Day' , chr(39) & '3. On Time -3 / + 1 Day' & chr(39),

if(GetFieldSelections(OTDStage) = '4. On Time -3 / + 2 Days', chr(39) & '4. On Time -3 / + 2 Days' & chr(39),

if(GetFieldSelections(OTDStage) = '5. On Time -5 / 0 Days', chr(39) & '5. On Time -5 / 0 Days' & chr(39),

if(GetFieldSelections(OTDStage) = '6. On Time -5 / + 1 Day', chr(39) & '6. On Time -5 / + 1 Day' & chr(39),

''))))))))

Result example in Qlik View Text Box: '1. Earlier -5'

Now I´ve a simple function that uses the variable:

sum({\$<OrderPhase_Desc = {'Revenue'}, OnTimeDelivery = {'\$(=vOTDStage)'} >}NettAmount\$(vCurrency))

The result is zero in Qlik Sense, not showing what I´m doing wrong. I´m sure I´m just overlooking something but I just can´t see it right now... As a second step I would like to replace the field "OnTimeDelivery" by another variable. Is that possible? I haven´t tried it before and I´m still stucked with the first one.

Many thanks for your help!!

• ###### Re: variable in set analysis

I believe the objective is to evaluate the expression only when one value in OTDStage is selected?

If  Yes, then drop the variable and use the below expression instead

=if(GetSelectedCount(OTDStage)=1,sum({\$<OrderPhase_Desc = {'Revenue'}>}NettAmount\$(vCurrency))

• ###### Re: variable in set analysis

Vineeth

There seems to be 2 fields in play here, but I agree that there might be a simpler approach to what is done here:

If(GetSelectedCount(OTDStage) = 1,

Sum({<OrderPhase_Desc = {'Revenue'}, OnTimeDelivery = P(OTDStage)>} NettAmount\$(vCurrency)),

Sum({<OrderPhase_Desc = {'Revenue'}, OnTimeDelivery = >} NettAmount\$(vCurrency)))

Not sure I understand what is needed when no OTDStage is selected or more than one is selected? You can to sum all OnTimeDelivery or the expression should be 0? If it needs to be 0, then may be this

If(GetSelectedCount(OTDStage) = 1,

Sum({<OrderPhase_Desc = {'Revenue'}, OnTimeDelivery = P(OTDStage)>} NettAmount\$(vCurrency)))

• ###### Re: variable in set analysis

Yes, I assumed both fields to be in the same table ; if not then the second expression you provided will definitely work.

• ###### Re: variable in set analysis

You are right, the variable isn´t necessary. The second formular works very close. All I had to do is to exchange the OnTimeDelivery Field by a variable which I have now designed:

Variable:

=if(GetSelectedCount(OTDStage)=1,

if(GetFieldSelections(OTDStage) = '1. Earlier -5', chr(39)& 'OnTimeDelivery' & chr(39),

if(GetFieldSelections(OTDStage) = '2. On Time -3 / 0 Days', chr(39)& 'OnTimeDelivery' & chr(39),

if(GetFieldSelections(OTDStage) = '3. On Time -3 / + 1 Day' , chr(39)& 'OnTimeDeliveryPlus1Day' & chr(39),

if(GetFieldSelections(OTDStage) = '4. On Time -3 / + 2 Days', chr(39)& 'OnTimeDeliveryPlus2Days' & chr(39),

if(GetFieldSelections(OTDStage) = '5. On Time -5 / 0 Days', chr(39)& 'OnTimeDeliveryMinus5Days' & chr(39),

if(GetFieldSelections(OTDStage) = '6. On Time -5 / + 1 Day', chr(39)& 'OnTimeDeliveryMinus5Plus1Day' & chr(39),

'')))))))

Expression:

If(GetSelectedCount(OTDStage) = 1,

(Sum({<OrderPhase_Desc = {'Revenue'}, \$(=vOTDField) = P(OTDStage)>} QuantitySalesUnit))   ,0)

The reason is that I´ve have redundant OTD stages per record (On Time Delivery Stage):

 1. Earlier -5 2. On Time -3 / 0 Days

3. On Time -3 / + 1 Day

4. On Time -3 / + 2 Days

 5. On Time -5 / 0 Days 6. On Time -5 / + 1 Day

E.g. I´ve got a table and a on time delivery for a record of -2 days. This record  is assigned to all the stages except then stage 1. Ealier -5. A record with a OTD of +2 days would be only counted only as Stage 4. On Time -3 / +2 Days. This means I´ve got more than just one stage per record because the user wants to see it in this way... For this reason I created a field for each of the stages. But the user wouldn´t understand that he has got several different fields to select just one of the OTD Stages. Therefore I created the field "OTDStage" as load inline and it is not connected to the table structure. Now with this prrocess for the user for him it looks like that he just choses one of the stages and he doesn´t has to care that Qlik View switches to the according OTD Field via the variable in the background.

Maybe there would have been an easier process - it is not fast but it works.

Just one more question: What is the meaning of "P" in P(OTDStage)? I haven´t seen this before.

Many thanks for all of your help!!

• ###### Re: variable in set analysis

I guess I don't understand your data well enough to look for another way, unless you would want to go that route and discuss it further. But with regards to p() and also, e() you, can look here

P() & E() and where do you use them?

• ###### Re: variable in set analysis

Hi Sunny,

that´s true, it´s difficult to talk about complex analysis here without examples which are hard to make because of complex data... However, you helped me with that link, too. The expression which I posted was only part of the real one to make it easier. So this is the real one:

If(GetSelectedCount(OTDStage) = 1,

((Sum({<OrderPhase_Desc = {'Revenue'},\$(=vOTDField) = P(OTDStage)>} NettAmount\$(vCurrency)))

- sum({\$<OrderPhase_Desc = {'Revenue'}, SalesOrderType = {'G10', 'R10', 'V15'}, \$(=vOTDField) = P(OTDStage) >}NettAmount\$(vCurrency))

- sum({\$<OrderPhase_Desc = {'Revenue'}, LineOfBusiness = {'Freight', 'Insurance', 'Packing', 'Missing'}, \$(=vOTDField) = P(OTDStage) >}NettAmount\$(vCurrency)))

,0)

Looks like E() will help me to avoid the minus caluclations because I was unable to express "exclude" for the SalesOrderType and the LineOfBusiness Fields before. That will make my expression shorter and maybe faster. I really learned something new today, thanks!!!

• ###### Re: variable in set analysis

So this is my new expression using E():

If(GetSelectedCount(OTDStage) = 1,

(Sum({<OrderPhase_Desc = {'Revenue'},\$(=vOTDField) = P(OTDStage), SalesOrderType = E({1<SalesOrderType={'G10', 'R10', 'V15'}>}), LineOfBusiness = E({1<LineOfBusiness={'Freight', 'Insurance', 'Packing', 'Missing'}>})   >} NettAmount\$(vCurrency)))

,0)

That´s really great!!!

• ###### Re: variable in set analysis

Carolin -

I am glad we were able to help you learn something new today

Happy New Year!!

Best,

Sunny

• ###### Re: variable in set analysis

Hi Carolin,

I think this may help you

vOTDStage

=if(GetFieldSelections(OTDStage) = '1. Earlier -5','OnTimeDelivery = {''1. Earlier -5''}')....................

And in Expression

sum({\$<OrderPhase_Desc = {'Revenue'},\$(vOTDStage) >}NettAmount\$(vCurrency))

Regards,

• ###### Re: variable in set analysis

This is what I had on my mind first. But then I realized that the On Time Delivery Field must be a variable...thanks for your support!