topic Re: Unable to take max date in QlikView

Unable to take max date

Thu, 30 Mar 2017 07:43:16 GMT

Hi,

I am trying to calculate the total by customer group based on the max trxdate of the customers.

For ex, consider the below table

Cust, trxdate, amt, Group

1, 01-01-2017, 190, A

1, 02-01-2017, 21, A

1, 03-01-2017, 23, A

2, 04-01-2017, 121, A

2, 05-01-2017, 232, A

3, 06-01-2017, 123, B

3, 07-01-2017, 121, B

my expected output will be

Group value

A 255(23+232) (03-01-2017 & 05-01-2017)

B 121

I tried the logic by writing set analysis,

aggr(sum({<trxdate_num={"=aggr(max(trxdate_num),cust)"}>}amt),Group)

but, the above expression returns the min date values,like for Group A, it returns 311(190+121).

can anyone help me on this issue.

Thanks in advance.

Re: Unable to take max date

Thu, 30 Mar 2017 08:21:50 GMT

Thanks for you prompt response, but its not working as expected.

Any other solutions...

Thanks in advance.

Re: Unable to take max date

sunny_talwar — Thu, 30 Mar 2017 08:31:49 GMT

May be this

Sum(Aggr(FirstSortedValue(amt, -trxdate), Cust, Group))

Sum(Aggr(If(trxdate = Max(Total <Cust> trxdate), amt), Cust, Group))

Re: Unable to take max date

Fri, 31 Mar 2017 05:57:38 GMT

Hi,

First expression is working fine. Is there any other way to do this instead of firstsortedorder method.

like any set analysis way...

Thanks in advance.

Re: Unable to take max date

sunny_talwar — Fri, 31 Mar 2017 07:57:18 GMT

You can use set analysis if you are willing to make flags in the script.... bu then these flags will be static and won't change based on selections.

The problem with set analysis is that it is evaluated once per chart. If you are looking to get different amt for each Group which has a different max trx_date. Set analysis can't get you this information... You will either need FirstSortedValue or Aggr() function to do this....

Re: Unable to take max date

Mon, 03 Apr 2017 05:39:48 GMT

Thanks Talwar.

If you dont mind, May i know what is wrong in the below expression, Why its return min date instead of max date.

aggr(sum({<trxdate_num={"=aggr(max(trxdate_num),cust)"}>}amt),Group)

Thanks in advance.

Re: Unable to take max date

sunny_talwar — Mon, 03 Apr 2017 08:35:38 GMT

You have once again used set analysis