Dynamic Date Expression- Set Analysis

[#1]

Hi Community users,

I have a formula in QV

=avg({$<Date={'$(=max(Date))'}>} Sales)

Problem : QV does not calculate for the Data in which Sales records does not exists for max date for Customers having last sales date of previous month.

Is there any formula ,that will calculate avg for all the records for max date and auto calculate for those in which max date is of previous month.

e.g Customer has sales on 01-Mar-14

while b has Sales on 28-Feb-2014, i want it will calculate the avg for Customer A on 01-Mar-14 while for B it should be 28-Feb-14

Thanks.

[nlmmaurya]

Try this Avg(if(Date=aggr(max(Date),Customer),Sales)) may be it will work.

[ashwanin]

You can try :  avg({<Date={'>=$(=addmonth(max(Date)))'}>} Sales)

[sanketkhunte]

Hi Krishna,

If you are using Qlikview 11 then you may go through Alternate States. It becomes easy to present Previous and Current month of data by using this Comparative feature.

Sanket

[#5]

Hi Neelam,

Can we do this by Set Analysis?

[#6]

Hi Ashwani,

This is somewhat near, but here it calculates the data of two dates.

[#7]

Hi Sanket,

Yes, i am using QV-11. Can you please let me know, more about this?

I dont want to compare data. I want qv will calculate for all customers, where their sales dates is of last month.

[#8]

try this

avg({$<Date={'$(=aggr(max(Date),Customer)'}>} Sales)

[#9]

Hi Bertran,

Might be i have written something wrong.

Here Customer is in Dimension. I need an expression which took the last date.

i tried below, but this is also not working .

avg({$<Date={'$(=aggr(max(Date)'}>} Sales)

[#10]

if you want to get the last date by Customer you need :

aggr(max(Date),Customer)