Where clause in qlikview pivot table

[absekrafi]

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

[tresesco]

May be something like:

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

[jensmunnichs · Accepted solution]

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]

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]

Hey,

Try

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

[absekrafi]

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]

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]

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]

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]

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