topic set-analysis lost customers in QlikView

set-analysis lost customers

Thu, 23 Jun 2011 06:03:39 GMT

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?

Daniel

set-analysis lost customers

Thu, 23 Jun 2011 07:56:15 GMT

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...

Hans

set-analysis lost customers

Thu, 23 Jun 2011 09:29:26 GMT

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?

Daniel

set-analysis lost customers

Thu, 23 Jun 2011 10:10:09 GMT

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])

Hans

Re: set-analysis lost customers

Thu, 23 Jun 2011 14:32:07 GMT

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

Re: set-analysis lost customers

johnw — Fri, 24 Jun 2011 00:11:25 GMT

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.

Re: set-analysis lost customers

Sun, 26 Jun 2011 02:27:33 GMT

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.

Hans

Re: set-analysis lost customers

Mon, 27 Jun 2011 07:12:45 GMT

Thank you Hans, that worked brilliantly!

Re: set-analysis lost customers

Mon, 27 Jun 2011 07:45:33 GMT

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...

Hans