# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
Creator

## Sum if greater than

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

1 Solution

Accepted Solutions
Creator

Hi,

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)

3 Replies
Creator

Hi,

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)

Creator
Author

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.

Creator

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!

Community Browser