topic Re: Evaluate max date in set analysis in QlikView

Evaluate max date in set analysis

csurajbhan — Wed, 17 Jun 2015 09:11:29 GMT

From Above table, i want Amount Paid to be calculated with conditions:-

1) Payment Date <= CalDate

2) Payment Date >= max(Delivery Date) with condition 1

for the time period of March-2015 month with linked to CalDate.

I used below expression , but it is not working.

Sum({< link_Date={">=$(vCurrentMonthStart)<=$(vCurrentDate)"} ,[Payment Date]={"<=$(vCurrentDate)"} >}

if(([Payment Date]>=vMaxDelDate ) , [Amount Paid]) )

here variable vMaxDelDate contains =Aggr(date(max([Delivery Date]),'DD-MM-YYYY'), link_Advance)

All these conditions has to give output as 55000

Kindly help me to get the output.

Re: Evaluate max date in set analysis

Gysbert_Wassenaar — Wed, 17 Jun 2015 17:00:59 GMT

Actually the sum is 110000. Both the third and fourth row satisfy the conditions. Payment Date 01-11-2014 is smaller than CalDate 31-03-2015. The max Delivery Date is 13-06-2014 and for both these two rows the Payment Date 01-11-2014 is larger than 13-06-2014. See attached qvw.

Re: Evaluate max date in set analysis

csurajbhan — Thu, 18 Jun 2015 06:09:35 GMT

Hi Gysbert,

Thanx for your input.

After Condition 1, we have to evaluate Max Delivery Date rows only, here it is row 2 & row 4.

after this, we have to check whether Payment Date >= Delivery Date, which results in row 4 as output.

Hence Amount Paid is only 55000 with row 4.

Kindly suggest,

Thanx in advance.