topic Re: Get max date in expression based on dimension in QlikView

Get max date in expression based on dimension

Mon, 27 Apr 2015 08:51:25 GMT

Hello,

I am using pivot table to show the inventory data.

For 1 material no, there are different batch dates.

So as on any selected date in calender, I want to consider only max batch date on or before calender date.

for eg:

if Jan 2015 is my calender date, and material no is 11106 and there are batch dates in June 2014, Dec 2014 and March 2015.

So as on Jan 2015, dec 2014 is the max batch date.

So how to write the expression using set analysis for this?

Re: Get max date in expression based on dimension

hariprasadqv — Mon, 27 Apr 2015 09:02:54 GMT

Hi,

Could you please post the sample data!

Re: Get max date in expression based on dimension

sunny_talwar — Mon, 27 Apr 2015 09:06:23 GMT

Like hariprasadqv‌ said it would be easier to give you a better suggestion if we have a sample to look at, but if you just need general guidance, I would suggest creating flags in the script where the data exits and then use that flag in your expression. Once you select a date, the max expression with the flag should get you to what you are looking for.

HTH

Best,

Sunny

Re: Get max date in expression based on dimension

Mon, 27 Apr 2015 09:09:13 GMT

Hello,

Sample data

material no	BatchDate	Amount
11106	08/22/2013	223.67
11106	09/26/2013	223.67
11106	12/31/2013	223.67
11106	02/28/2014	223.67
11106	03/14/2014	223.67
11106	04/23/2014	223.67
11106	08/25/2014	223.67
11106	08/30/2014	223.67
11106	09/02/2014	223.67
11106	09/10/2014	223.67
11106	09/19/2014	223.67
11106	12/31/2014	223.67

So from the above list, it should show only 12/31/2014.

For this currently, I have expression as

num(sum({$<Year = , Month =, Day =, PostingDate = {'>=$(=Date(MonthStart(min(CalenderDate)))) <=$(=Date(max(CalenderDate)))'} >} qty),'#,###.000')

In this expression, I want to add 1 more condition for batch date.

Re: Get max date in expression based on dimension

Mon, 27 Apr 2015 09:12:03 GMT

In General You Could Use MAX in the set analysis and Nullify the Month Selection In the Expression so That always max of month will show even if u select the any month..

But if u provide sample data we can sort out the exact solution ..

Re: Get max date in expression based on dimension

sunny_talwar — Mon, 27 Apr 2015 09:19:39 GMT

Have a look at the attached qvw. Let me know if this helps.

Best,

Sunny

Re: Get max date in expression based on dimension

Mon, 27 Apr 2015 09:28:55 GMT

Hello hariprasadqv,

I already have a set analysis expression as given in earlier reply.

I have to add 1 more condition in the same.

I will explain again.

If I select Jan 2015, i should get DEc 2014 as the batch date.

If I select Oct 2014, I should get 09/19/2014 as the batch date.

So in this way, as per the selection in the calender, batch date will change.

Re: Get max date in expression based on dimension

Mon, 27 Apr 2015 09:30:37 GMT

Try This

=Num(sum({$<Year = , Month =,Date={"$(=Max(Date))"}>}Amount ),'#,###.000')

Re: Get max date in expression based on dimension

hariprasadqv — Mon, 27 Apr 2015 10:15:35 GMT

Subbarja approach will resolve your issue.

Re: Get max date in expression based on dimension

Mon, 27 Apr 2015 10:33:39 GMT

so you mean, I should write my expression as below:

num(sum({$<Year = , Month =, Day =, PostingDate = {'>=$(=Date(MonthStart(min(CalenderDate)))) <=$(=Date(max(CalenderDate)))'} , BatchDate = {'$(=Date(=Max(BatchDate)))'} >} qty),'#,###.000') ?

I tried with this, but it is not giving any output.

Re: Get max date in expression based on dimension

buzzy996 — Mon, 27 Apr 2015 10:38:56 GMT

may be u can try this way,u can simply setup ur calendar date with batch date,tht helps to bring the corresponding values into ur dashboard.

Re: Get max date in expression based on dimension

Tue, 28 Apr 2015 06:37:54 GMT

No, I can't do like that because I have posting date also and depending on that only I created Calender date.

Can you have another solution. Please reply me.