topic Re: Using aggr and total with date ranges in QlikView

Using aggr and total with date ranges

Thu, 25 Apr 2013 18:44:51 GMT

I am trying to create a chart with the number of new customers that we have sold to this week. Where new is defined as anyone who has not bought from us in a set timeframe. (Eg last 3 years)

Idea of chart would be:

Weekend	# of new customers
4/21/2013	50
4/14/2013	27
4/07/2013	13
3/31/2013	68
3/24/2013	44

The underlying table basically looks like this:

OrderId, AccountId, Date, Amount

I have gotten close with this:

Dimension: Weekend(Date)

Expression: sum(aggr(if(sum(Amount) = sum(TOTAL<AccountId> Amount),1,0),Date,AccountId))

The problem is that the Total is not date bound by the Order Date, and will pick up both older orders than we want and more recent orders which also want to be excluded.

Any help or direction would be much appreciated

Nick Clift

DVL Inc

Re: Using aggr and total with date ranges

rustyfishbones — Thu, 25 Apr 2013 19:21:02 GMT

Hi Nick,

try the following,

Create a Variable and then paste this into it:

Date = {">=$(=DATE(ADDMONTHS(FLOOR(MONTHSTART(MAX(TOTAL Date))), -35)))<=$(=DATE(FLOOR(MONTHEND(MAX(TOTAL Date)))))"}

Call the variable

last.3.years

Then use the following Expression

SUM({<$(last.3.years)>}Amount)

Hope that helps

Re: Using aggr and total with date ranges

Thu, 25 Apr 2013 19:55:53 GMT

Thanks for the reply Alan and it is a little helpful. The problem I am still running into is that it is not relative to the particular row in the chart. More explictly MAX(TOTAL Date) always resolves to the same thing.

What I am trying to find is something I can put in a sum( ) expression that will resolve to something different depending on the row.

Using the previous table as an example

Weekend	# of new customers
4/21/2013	50 <- sum() Calculated on the date range 4/21/2013 4/22/2010
4/14/2013	27 <- sum() Calculated on the date range 4/14/2013 4/14/2010
4/07/2013	13 <- sum() Calculated on the date range 4/07/2013 4/07/2010
3/31/2013	68 <- sum() Calculated on the date range 3/31/2013 3/31/2010
3/24/2013	44 <- sum() Calculated on the date range 3/24/2013 3/24/2010

I am not entirely certain it is possible to do via an expression.

Nick

Re: Using aggr and total with date ranges

rustyfishbones — Thu, 25 Apr 2013 20:26:31 GMT

Could you send me a copy of the file and I will have a look, I am trying to learn as much as I can so I want to help, it's the only way I can learn quickly

Thanks

Re: Using aggr and total with date ranges

Thu, 25 Apr 2013 20:43:23 GMT

I would try to resolve the logic in the load script.

You have the dates, and you can get the weeks with week(Date), so you can count new customers grouping by weeks if that's what you need.

I would add a column as a Flag to new customers, after that, you only need at the straight table an expression like sum(FlagNewCustomer) using that flag.

I hope this helps

I would need more details to help yo with the logic