Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for
Did you mean:
Creator III

## Where clause in qlikview pivot table

Dear community,

I am looking to find the minimum date where stock, a field in my pivot table, different than 0.

How can I write that?

Thank you,

Abdallah

3 Solutions

Accepted Solutions
Creator III
Where clauses only work in script, in charts you have to use set analysis. Your expression would be something like:

Min({<StockField -= {0}>} DateField)
Creator III

Found my mistake. Try:

Sum({<TRANSACTION_DATE = {'\$(=Min({<STOCK -= {0}>} TOTAL TRANSACTION_DATE))'}>} TOTAL STOCK)

Creator III
Don't have time to check this right now, but maybe changing to 'greater than 0' instead of 'not 0' could fix the issue:
{<STOCK -= {0}>} ---> {<STOCK = {">0"}>}
14 Replies
MVP

May be something like:

`=Min({<stock-={0}>}date)`

Creator III
Where clauses only work in script, in charts you have to use set analysis. Your expression would be something like:

Min({<StockField -= {0}>} DateField)
Creator III
Author

Hi again,

Thank you for the prompt answers.

Min({<STOCK -= {0}>} TRANSACTION_DATE) is giving me the minimum date. Now I want to use set analisys to calculate the

sum, the expression bellow is not working.

=Sum(TRANSACTION_DATE = {Min({<STOCK -= {0}>} TRANSACTION_DATE)}>}STOCK).

Best regards,

Abdallah

Creator III

Hey,

Try

Sum({<TRANSACTION_DATE = {"=Min({<STOCK -= {0}>} TRANSACTION_DATE)"}>} STOCK)

Creator III
Author

Hi jensmunnichs,

I tryied the expression you proposed but the sum isn't returned. The minimum data is returned.

Enclosed the data:

as you can see, for the date 01/01/2018, 2 values of STOCK and min stock are reurned.

I want them to be added.

first value : 570.

second value : 10618.

Thank you,

Creator III
Sum({<TRANSACTION_DATE = {"=Min({<STOCK -= {0}>} TRANSACTION_DATE)"}>} STOCK)

This expression (same as my last post) should return the sum of STOCK where TRANSACTION_DATE is equal to the minimum TRANSACTION_DATE where stock is not 0. The only reason I can think of where this wouldn't return the expected value of 570 + 10618 would be an issue in your dimension(s). What dimension(s) are you using in the chart with this expression?
Creator III
Author

Hi,

The dimensions I am using (showing) in my pivot table are:

1. TRANSACTION_DATE

2. STOCK

3. Product name

4. Product codes

and as expressions:

1. min date (where stock <> 0)

2. Min stock (for the found min date).

Thank you in advance,

Abdallah

Creator III
I see what's happening now. Since you're using these expressions in a table, and you have those dimensions, it's calculating the expressions over the dimensions used in the table. So since TRANSACTION_DATE is one of these dimensions, min(TRANSACTION_DATE) will always return the same value (which is why your min date is always the same as the TRANSACTION_DATE in the picture you attached in your previous post).

So you have 2 options here:
- Create 2 text boxes and enter the expressions in those (remember to start with a '=' or it won't calculate the expression. Now, because you have no dimensions, it will find the min date where stock <> 0 across your entire data set
- Add TOTAL to the expressions to ignore dimension in your table. This would mean that the value is the same in every row of your pivot table though, don't know if that is more readable than a couple of text boxes, but that's down to preference. The expressions would be:
1. Min({<STOCK -= {0}>} TOTAL TRANSACTION_DATE)
2. Sum({<TRANSACTION_DATE = {"=Min({<STOCK -= {0}>} TOTAL TRANSACTION_DATE)"}>} TOTAL STOCK)

Hope that solves your problem. If not let me know.
Creator III
Author

Hi Jensmunnichs,

I tried the second option : The min date is returned but the minimum stock is not returned.

Now the expression of min STOCK, with 'TOTAL' added or not, is returning the sum of all STOCK value between the 2 dates:

vStartDate = Min(TRANSACTION_DATE)

vEndDate = Max(TRANSACTION_DATE).

Just to mention that I want the Sum of STOCKs of the Min(TRANSACTION_DATE) where STOCK is not null only. Because I might

find 2 or more values of the stock of same product (different sub-products codes).

In the bellow tables, the Min stock value should be 3393,000.

 Start Date = 10/10/2018 End Date = 12/10/2018

 LIB_PRODUIT Min trans date Min stock 10/10/2018 9750 PHOSPHATE 10/10/2018 9750

 LIB_PRODUIT TRANSACTION_DATE STOCK Sum stock PHOSPHATE 10/10/2018 114 114 PHOSPHATE 10/10/2018 3279 3279 PHOSPHATE 11/10/2018 973 973 PHOSPHATE 11/10/2018 2296 2296 PHOSPHATE 12/10/2018 792 792 PHOSPHATE 12/10/2018 2296 2296 Total Total Total 9750

Community Browser