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.

[#2]

Please, try this.

Let me know, if it helps and if you like it....

[jonathandienst]

Hi

If the table dimension is Customer, you will not be able to use a set expression for this analysis as the set is evaluated once for the object, not on each line.

You can try this:

     Sum(If(Date = Max(Aggr(Date, Customer)), Sales))

If the data set is large, it would be better to store a last sale date field (associated with to the customer), or set a last transaction flag value (associated with the sales fact). For the former, you could now use:

     Sum(If(Date = LastSaleDate, Sales))

HTH

Jonathan

[#4]

Hi, KRISHNA, Jonathan.

In fact, there is a possibility to calculate this formula using set expression, but you need to have 'RowID' field (or similar) in your data. Then you can use such expression:

avg({< RowID = {"=Date = aggr( max(total<Customer> Date ), Customer, RowID )"} >} Sales)

You can use this expression when you want to calculate this formula depends on current selection in date filters (e.g. Year, Month and so on).
If you just want to show absolute result for customer, the best way is using additional field with flag, as Jonathan wrote above.

Nik.