topic sum(sales) only for returning customers between 2 dates...how? in QlikView

sum(sales) only for returning customers between 2 dates...how?

wizardo — Wed, 09 Sep 2009 22:12:37 GMT

hi good fellas,

i need to sum the sales only for customers who bought more then once between 2 (user selected) Dates.

...

the user have selected 01/02/2009 - 31/03/2009

i want only customers who bought more then once during that period to be included.

there are other filters involved but thats the only one i couldnt figure out

any help would be greatly appreciated

Mansyno

sum(sales) only for returning customers between 2 dates...how?

Wed, 09 Sep 2009 22:23:53 GMT

What are your dimensions?

In Set Analysis, you would use something like:

Sum({DATE = {'>=01/02/2009<=31/03/2009'}>} Sales)

If your dates are variables, you could use this to make it dynamic:

Sum({DATE = {'>=$(=vDate1)<=$(=vDate2)'}>} Sales)

I don't think you need quotes around the individual dates.

sum(sales) only for returning customers between 2 dates...how?

wizardo — Wed, 09 Sep 2009 22:39:09 GMT

thanks for you answer,

maybe i was not clear enough.

i know how to use a date range in set analysis or anywhere else

but how do i only include returning customers ?

my dimension is ofcourse customer

Mansyno

sum(sales) only for returning customers between 2 dates...how?

Wed, 09 Sep 2009 23:01:39 GMT

Yeah, it looks like I misread it.

I'm not sure that you can use Count(Field) in your Set Analysis expression. You could do it with an if...then, but it's a little messy:

If(Count({SA}Amount)>1, Sum ({SA}Amount), Null())

You would need to use the Set Analysis in both aggregates to make sure that the count only applies to the entered date span.

sum(sales) only for returning customers between 2 dates...how?

Wed, 09 Sep 2009 23:03:12 GMT

Mansyno,

maybe you should include a count on customer (count >1)

Rey-man

sum(sales) only for returning customers between 2 dates...how?

wizardo — Wed, 09 Sep 2009 23:40:42 GMT

of course a filter on count > 1 should be included

but thats exactly the question

where and how

set analysis or if

etc

sum(sales) only for returning customers between 2 dates...how?

Thu, 10 Sep 2009 00:47:48 GMT

In the expression where you give sum(Sales), you can use something like thisif(count(CustomerID)>1,sum(Sales),sum(0))

And make sure that you have checked Supress 0 Values. Then you will get the result you want i think.

sum(sales) only for returning customers between 2 dates...how?

Thu, 10 Sep 2009 01:11:23 GMT

Do you want customers who returned within the date span? Or just customers who have been there more than once ever?

If you want within the date span, then you have to use Set Analysis within the Count and within the Sum, like I posted above. If it's the second scenario, then no Set Analysis is needed on the count.

sum(sales) only for returning customers between 2 dates...how?

wizardo — Thu, 10 Sep 2009 02:15:37 GMT

thank you both for your solutions but...

if i use ither of your solutions then i will still have ALL the customers in the table. the ones that are returning will have thir sum(sales) and the rest will have 0

what i need is to have only the customers that are returning apear in the table

i need

count( if(cond.) field)

i dont need

if(cond.) then count

or to be more exact

i need nested aggr

problem is if i used AGGR() will be to heavy on the table ( i think, cuz i have LOTS of data)

sum(sales) only for returning customers between 2 dates...how?

Thu, 10 Sep 2009 02:33:16 GMT

Use the Set Analysis in all of your expressions on the table. Then for those customers that aren't returning, you will have zeroes for everything. Then you can use the Chart property Suppress Zero Values to hide all of those records.

I think the expression that you say you want and the one you don't want are logically equivilent. If the condition is false, you get 0 for both of them. If the condition is true, you get the count for both of them.

If you're trying to exclude those customers that don't meet your criteria, you need to make all of the expression result in zero and then suppress them. If any expression doesn't result in zero, it would be suppressed.

sum(sales) only for returning customers between 2 dates...how?

Thu, 10 Sep 2009 02:41:56 GMT

hi wizardo, as u said the non returning customers will be having sales 0. Then in the property of the chart, there is something like 'Suppress Zero Values'. On selecting that, customers with 0 sales won't be displayed. In this case, it means, non-returning customers won't be displayed.