Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Just replace the year by the corresponding variable.
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)
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.
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.