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

[wizardo]

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

[#2 · Accepted solution]

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.

[wizardo]

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

[#4]

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.

[#5]

Mansyno,

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

Rey-man

[wizardo]

of course a filter on count > 1 should be included

but thats exactly the question

where and how

set analysis or if

etc

[#7]

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.

[#8]

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.

[wizardo]

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)

[#10]

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.