topic Re: Sum if greater than in QlikView

Sum if greater than

leenlart — Fri, 14 Sep 2018 10:30:37 GMT

Hello,

I am stuck on a sum problem and I think I need to use AGGR but I'm not very adept at implementing this function yet.

So I have a table : SITUATIONS with the stock situations by date. In this table we have the quantity, value, and state of the Batch number for each item in stock. I have another table CRITERE which has the criteria for each batch number. The criteria are expiration date, sell-by date and use-by date. Each criteria has a VALD_LOTcrit for the date that criteria goes into affect. For exemple the criteria DLC will go into affect the 31/8/2018 for a batch. Each batch does not have an entry in the criteria table for the three criteria we are checking.

I am trying to build a table that will calculate the provision based on the state of the batch and based on the criteria.

To do this step by step, I have added the batch as the dimension, and my expressions are

1. the value * the percentage based on the state,

2. the value if there is a DLC criteria and the VALD_LOTcrit is <= the date selected,

3. the value if there is a DLUO criteria and the VALD_LOTcrit is <= the date selected

4. the value if there is a DLS criteria and the VALD_LOTcrit is <= the date selected

Expression 1 is OK. For the others I seem to have hit a wall.

I have tried this :

if ( CCRI_LOT = '$DLC' and VALD_LOTcrit <= DATEJOUR, sum( MTPXREVSTAT_STD_S_SIT), 0)

but it returns 0.

And I have tried this :

sum( aggr( if (CCRI_LOT = '$DLC' and VALD_LOTcrit <= [DATEJOUR] , MTPXREVSTAT_STD_S_SIT), LOT_SIT))

but it also returns 0.

Does anyone have any ideas ?

Thanks!!

Re: Sum if greater than

captain89 — Fri, 14 Sep 2018 10:45:17 GMT

Hi,

please attach a sample table.

I'm pretty sure that you can do it with set analysis... try this:

create a variable like vDATEJOUR with a variable box.

sum({< CCRI_LOT = {'$DLC'} , VALD_LOTcrit= {"<=$(vDATEJOUR)"}>} MTPXREVSTAT_STD_S_SIT)

Re: Sum if greater than

leenlart — Fri, 14 Sep 2018 10:57:10 GMT

I totally didn't believe that it would work, but it does!! Thanks!!

I was using DATEJOUR which is the field with the end date of a month in my calendar. I don't understand why this didn't work.

So I created the variable vDATEJOUR, and I set it = DATEJOUR.

If you can explain why a variable works better, I would really appreciate it. It might help me to avoid hitting more walls in the future, as my nose is getting sore.

Re: Sum if greater than

captain89 — Fri, 14 Sep 2018 11:52:27 GMT

You can't compare more than 1 value (VALD_LOTcrit) with another field with more than 1 value (DATEJOUR).

If you use an expression that let you get one value (for example max(DATEJOUR)) you can compare one field with another but i think that a variable is a better way to do that for you in this case.

try without the variable

sum({< CCRI_LOT = {'$DLC'} , VALD_LOTcrit= {"<=$(=max(DATEJOUR))"}>} MTPXREVSTAT_STD_S_SIT)

maybe it runs anyway

Sorry for my english I'm italian. I must improve it.

Thanks for the mark!