topic Re: Where clause in qlikview pivot table in QlikView

Where clause in qlikview pivot table

absekrafi — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Where clause in qlikview pivot table

tresB — Thu, 29 Nov 2018 11:03:14 GMT

May be something like:

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

Re: Where clause in qlikview pivot table

jensmunnichs — Thu, 29 Nov 2018 11:07:36 GMT

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

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

Re: Where clause in qlikview pivot table

absekrafi — Thu, 29 Nov 2018 15:12:51 GMT

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

Re: Where clause in qlikview pivot table

jensmunnichs — Fri, 30 Nov 2018 07:16:08 GMT

Hey,

Try

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

Re: Where clause in qlikview pivot table

absekrafi — Fri, 30 Nov 2018 10:59:00 GMT

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,

Re: Where clause in qlikview pivot table

jensmunnichs — Fri, 30 Nov 2018 14:32:29 GMT

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?

Re: Where clause in qlikview pivot table

absekrafi — Mon, 03 Dec 2018 15:15:01 GMT

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

Re: Where clause in qlikview pivot table

jensmunnichs — Mon, 03 Dec 2018 15:33:27 GMT

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.

Re: Where clause in qlikview pivot table

absekrafi — Tue, 04 Dec 2018 13:08:27 GMT

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

Re: Where clause in qlikview pivot table

jensmunnichs — Tue, 04 Dec 2018 13:35:49 GMT

Found my mistake. Try:

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

Re: Where clause in qlikview pivot table

absekrafi — Tue, 04 Dec 2018 14:45:38 GMT

Hi again Jensmunnichs,

Now the expression sum(stock) is giving good results when the interval of dates does not contain null value (0: as stock).

I changed the date interval by changing start date and end date and the expression: =Min({<STOCK -= {0}>} TRANSACTION_DATE) is returning the date where STOCK = 0, but it should return the next date where STOCK <> 0.

Any idea on how to fix this problem?

Best regards,

Abdallah

Start Date	=	23/11/2014
End Date	=	30/11/2014

CODE_PROD	LIB_SS_PRODUIT	ProductCodeKey
3	PHOSPHATE	7-1--
3	PHOSPHATE	7-2--

LIB_PRODUIT	Min transac date	Min stock
	23/11/2014	0
PHOSPHATE	23/11/2014	0

LIB_PRODUIT	TRANSACTION_DATE	STOCK	Sum stock
PHOSPHATE	23/11/2014	0	0
PHOSPHATE	24/11/2014	0	0
PHOSPHATE	24/11/2014	625	625
PHOSPHATE	24/11/2014	-	0
PHOSPHATE	25/11/2014	241	241
PHOSPHATE	25/11/2014	1130	1130
PHOSPHATE	25/11/2014	-	0
PHOSPHATE	26/11/2014	940	940
PHOSPHATE	26/11/2014	1166	1166
PHOSPHATE	26/11/2014	-	0
PHOSPHATE	27/11/2014	334	334
PHOSPHATE	27/11/2014	759	759
PHOSPHATE	28/11/2014	44	44
PHOSPHATE	28/11/2014	228	228
PHOSPHATE	29/11/2014	25	25
PHOSPHATE	29/11/2014	815	815
PHOSPHATE	29/11/2014	-	0
PHOSPHATE	30/11/2014	51	51
PHOSPHATE	30/11/2014	58	58
PHOSPHATE	30/11/2014	-	0
Total	Total	Total	6416

Re: Where clause in qlikview pivot table

jensmunnichs — Tue, 04 Dec 2018 15:02:57 GMT

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"}>}

Re: Where clause in qlikview pivot table

absekrafi — Tue, 04 Dec 2018 15:34:52 GMT

Thank you,

At the first check it is working.

I will check all the possibilities, by changing start date and end date, and I will let you know.

Many thanks again,

Abdallah

Re: Where clause in qlikview pivot table

absekrafi — Wed, 05 Dec 2018 09:00:05 GMT

Hi Jensmunnichs,

I did my tests and the 2 expressions are working.

Thank you very much.

Abdallah