Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

absekrafi
Contributor 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
jensmunnichs
Contributor III

Re: Where clause in qlikview pivot table

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

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

Re: Where clause in qlikview pivot table

Found my mistake. Try:

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

jensmunnichs
Contributor III

Re: Where clause in qlikview pivot table

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
MVP

Re: Where clause in qlikview pivot table

May be something like:

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

 

 

jensmunnichs
Contributor III

Re: Where clause in qlikview pivot table

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

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

Re: Where clause in qlikview pivot table

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

Can you please help me on that.

Best regards,

Abdallah

jensmunnichs
Contributor III

Re: Where clause in qlikview pivot table

Hey,

Try

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

absekrafi
Contributor III

Re: Where clause in qlikview pivot table

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,

 

 

jensmunnichs
Contributor III

Re: Where clause in qlikview pivot table

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?
absekrafi
Contributor III

Re: Where clause in qlikview pivot table

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

jensmunnichs
Contributor III

Re: Where clause in qlikview pivot table

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.
absekrafi
Contributor III

Re: Where clause in qlikview pivot table

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_PRODUITMin trans dateMin stock
 10/10/20189750
PHOSPHATE10/10/20189750

 

LIB_PRODUITTRANSACTION_DATESTOCKSum stock
PHOSPHATE10/10/2018114114
PHOSPHATE10/10/201832793279
PHOSPHATE11/10/2018973973
PHOSPHATE11/10/201822962296
PHOSPHATE12/10/2018792792
PHOSPHATE12/10/201822962296
TotalTotalTotal9750