Set Analysis - retension of clients from prior year

[zagzebski]

I am inclduinjg a spreadsheet of what  am trying to do with set analysis. Basically I want to know what Dept/Client combinmations I have retained from a prior year. So the 2013 data should ONLY include combinations that are established from the 2012 expression.

I have been really struggling with this so any help is greatly welcomed! And please let me know of any followup questions you have to the data...

Steve

[Oleg_Troyansky]

Steve,

you can probably construct a very complex expression with the intersection of Dept and Client sets, or you can simplify your job tremendously if you can add one more field that stores a combination of Dept and Client - something like:

load

...

Dept & '|' & Client as  DeptClientCombo,

...

Then, your Set Analysis condition is relatively straightforward - select those DeptClientCombo values that had sales last year:

sum(  {<DeptClientCombo = {"=sum( {<Year = {2012}>}  Sales)>0"} >}  Sales)

the inner sum(), highlighted in bold, is serving as a search condition for your combination of Clients and Departments.

cheers,

Oleg Troyansky

www.masterssummit.com

[zagzebski]

Thanks Oleg. Looks like this would work. However would wouold get added to this expression if I wanted to see 2013 sales BUT ONLY for those DeptClientCombo's that had sales in 2012?

Steve

[jolivares]

Just replace the year by the corresponding variable.

[Oleg_Troyansky]

Steve, I don't think I understand your question... This formula calculates total available sales ONLY for those DeptClientCombos that had sales in 2012. If you wanted to add a condition of year 2013, then add it as another qualifier:

sum(  {<Year = {2013}, DeptClientCombo = {"=sum( {<Year = {2012}>}  Sales)>0"} >}  Sales)

If you want to exclude those DeptClientCombo that had sales in 2012, you'd use the boolean subtraction, replacing the equal sign "=" with "-=":

sum(  {<Year = {2013}, DeptClientCombo -= {"=sum( {<Year = {2012}>}  Sales)>0"} >}  Sales)

[zagzebski]

Thanks again. seems like I would need some kind of aggragation since the object only has Dept in the dimension. But I will give it a try - thanks for helping.

[christophebrault]

Hi,

Can you try something like :

sum({$<Client = p({<Year={$(=max(Year)-1)}>}Client )>}Sales)

This set analysis will only give you Sales for Client existing in past year.