Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
absekrafi
Creator III
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

14 Replies
jensmunnichs
Creator III
Creator III

Found my mistake. Try:

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

absekrafi
Creator III
Creator III
Author

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_PRODLIB_SS_PRODUITProductCodeKey
3PHOSPHATE7-1--
3PHOSPHATE7-2--

 

LIB_PRODUITMin transac dateMin stock
 23/11/20140
PHOSPHATE23/11/20140

 

LIB_PRODUITTRANSACTION_DATESTOCKSum stock
PHOSPHATE23/11/201400
PHOSPHATE24/11/201400
PHOSPHATE24/11/2014625625
PHOSPHATE24/11/2014-0
PHOSPHATE25/11/2014241241
PHOSPHATE25/11/201411301130
PHOSPHATE25/11/2014-0
PHOSPHATE26/11/2014940940
PHOSPHATE26/11/201411661166
PHOSPHATE26/11/2014-0
PHOSPHATE27/11/2014334334
PHOSPHATE27/11/2014759759
PHOSPHATE28/11/20144444
PHOSPHATE28/11/2014228228
PHOSPHATE29/11/20142525
PHOSPHATE29/11/2014815815
PHOSPHATE29/11/2014-0
PHOSPHATE30/11/20145151
PHOSPHATE30/11/20145858
PHOSPHATE30/11/2014-0
TotalTotalTotal6416

 

 

jensmunnichs
Creator III
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"}>}
absekrafi
Creator III
Creator III
Author

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

absekrafi
Creator III
Creator III
Author

Hi Jensmunnichs,

I did my tests and the 2 expressions are working.

Thank you very much.

Abdallah