variable in set analysis P() and E()

[carolin01]

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!!

[vinieme12]

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

[sunny_talwar · Accepted solution]

vinieme12‌

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

[vinieme12]

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

[dineshraj]

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,

[carolin01]

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!!

[carolin01]

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!

[sunny_talwar]

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() &amp; E() and where do you use them?

[carolin01]

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!!!

[carolin01]

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!!!