set-analysis lost customers

[#1]

Hi,

Im trying to create a set analysis expression to meassure the number of customer that was billed last month but not this month. my expression looks like this:

count({$<CLIENT_NAME={"=sum({$<Month={"*"}, Year={"*"}, [Year Week]={"*"},  [Week]={"*"}, PeriodCounter={"$(#=max(PeriodCounter))"}>} [Bill Amount])>0"}>}distinct CLIENT_NAME)

but it dosent work, does anyone have an idea how i could solve this?

BR

Daniel

[#2]

Hi

If You use Year={"*"} you select all of the years.... Instead you want to disregard the selections in the field.

And you wanted last months but not this months...

So I would do something like this:

count(

{

$<CLIENT_NAME={"=sum({$<Month=, Year=, [Year Week]=,  [Week]=, PeriodCounter={"$(#=max(PeriodCounter)-1)"}>} [Bill Amount])>0"}>

*

$<CLIENT_NAME={"=sum({$<Month=, Year=, [Year Week]=,  [Week]=, PeriodCounter={"$(#=max(PeriodCounter))"}>} [Bill Amount])=0"}>

}

distinct CLIENT_NAME)

To SETS... One for the billing last months where Bill Amount > 0 combined with one for current month where Bill Amount =0. They are combined with a Intersection, i.e. Both of the sets must be fullfilled...

BR

Hans

[#3]

Hi

I tried that but the expression gives null:

=count({$<[Client ID]={"=sum({<PeriodCounter={"$(#=max(PeriodCounter)-1)>}[Bill Amount])>0"}*{"=sum({<PeriodCounter={"$(#=max(PeriodCounter))>}[Bill Amount])=0"}>} distinct [Client ID])

the expression

sum({<PeriodCounter={"$(#=max(PeriodCounter)-1)>}[Bill Amount])

and

sum({<PeriodCounter={"$(#=max(PeriodCounter))>}[Bill Amount])

gives the correct values but when i combine then the result is null, do i need to do an aggr or something like that?

BR

Daniel

[#4]

Hi,

I would go for the Set-Analysis...

Where do you use the expression? Graph? Textbox? If Graph, what dimensions...

Yours expression, do they work? I miss a second " in the expressions.

Do you have an example of your qvw?

Everything you write in ={"..."} should work in the searchbox af al list field.

So if you use =sum({<PeriodCounter={$(#=max(PeriodCounter)-1)>}[Bill Amount])>0 and sum({<PeriodCounter={$(#=max(PeriodCounter))>}[Bill Amount]) = 0 as a search filter in the ClientId listbox.

It should work to use the same expression in a Set analysis statement.

count({1<[Client ID]={"sum({1<PeriodCounter={$(#=max(PeriodCounter)-1)>}[Bill Amount])>0 and sum({1<PeriodCounter={$(#=max(PeriodCounter))>}[Bill Amount]) = 0"}>} distinct [Client ID])

BR

Hans

[#5]

Hi

Thanks for you help hansiola. But i tried both expression in textboxes, one give 0 and the other null. I attached a file and hope someone can help me..

Thanks in advance

/Daniel

[johnw]

I barely glanced at the thread, so apologies if this example is totally useless, but it's an example of calculating new, lost and retained customers from year to year.  It doesn't use set analysis for the counts.

[#7 · Accepted solution]

Hej,

Nice app from John, but I dident find it to calculate correctly in the retaind field???

You might wan't the reason, why your set expression diden't calculate...

I attached an app with the set analysis like:=count({$<PeriodCounter=,CLIENT_NAME={'=sum({1<PeriodCounter={"$(=max(PeriodCounter)-1)"}>} [Bill Amount])>0 and (sum({1<PeriodCounter={"$(=max(PeriodCounter))"}>} [Bill Amount])=0 )'}>}distinct CLIENT_NAME)

As you can seee, if you use a nested expression, not both of them can use ". And also since you using an expression as a selection, you are better of using 1 as a Set Identifier.

BR

Hans

[#8]

Thank you Hans, that worked brilliantly!

[#9]

Daniel: You are welcome!

John: nice app. I wrote that I find an error in the calculation.

That was all in my app and my doing. I don't find any error...

Just wanted to clearify my previus post...

BR

Hans