topic Re: Dynamic Date Expression- Set Analysis in QlikView

Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 10:45:51 GMT

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.

Re: Dynamic Date Expression- Set Analysis

nlmmaurya — Mon, 14 Apr 2014 11:18:34 GMT

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

Re: Dynamic Date Expression- Set Analysis

ashwanin — Mon, 14 Apr 2014 11:32:39 GMT

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

Re: Dynamic Date Expression- Set Analysis

sanketkhunte — Mon, 14 Apr 2014 11:35:04 GMT

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

Re: Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 11:48:15 GMT

Hi Neelam,

Can we do this by Set Analysis?

Re: Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 11:49:10 GMT

Hi Ashwani,

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

Re: Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 11:51:40 GMT

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.

Re: Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 11:56:46 GMT

try this

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

Re: Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 12:11:34 GMT

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)

Re: Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 12:16:19 GMT

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

aggr(max(Date),Customer)

Re: Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 13:02:12 GMT

Please, try this.

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

Re: Dynamic Date Expression- Set Analysis

jonathandienst — Mon, 14 Apr 2014 13:35:06 GMT

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

Re: Dynamic Date Expression- Set Analysis

Mon, 14 Apr 2014 14:25:34 GMT

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.