Help with set analysis

[#1]

I want to create a measure that will give me YTD sales if the the previous year's sales are 0.

Here are my expression of PY Sales and YTD Sales:

PY:

sum({<[Year]={$(=max(Year)-1)}>} IF(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT))

YTD:

sum({$<[Date]={'<=$(=date(max([Date])))'}, [Year]={$(=max([Year]))}>} if(STATUS=31,EXTSHIPAMT*-1,EXTSHIPAMT))

I tried using an if statement, but it does not work for some reason:

if(sum({<[Year]={$(=max(Year)-1)}>} IF(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT))=0, sum({$<[Date]={'<=$(=date(max([Date])))'}, [Year]={$(=max([Year]))}>} if(STATUS=31,EXTSHIPAMT*-1,EXTSHIPAMT)))

Any help is appreciated!

Thanks,

James

[reddy-s]

Hi James,

I think you are complicating the analysis over periods. A master calendar will make your life easier.

Creating A Master Calendar

Once you have the master calendar

just a simple expression like this:

sum({$< YTD = {1}, Dim = {p({< Sales = {">0"}>}Dim)} >})

Thanks,

Sangram

[Gysbert_Wassenaar]

Are you trying to do this in a chart with a year dimension? If so you need to consider that the set of a set analysis expression is calculated at the chart level, not the row level. On a row for year 2015 you can't retrieve/show data of 2014, only of 2015.

[#4]

I'm trying to get the total sales in a KPI.  I have it working at the chart level by creating a dimension for new accounts (or accounts that did not buy anything the previous year):

aggr(if(sum({<[Year]={$(=max(Year)-1)}>} IF(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT))=0,[Cust Name]),[Cust Name])

This works great in a table, but I have to check exclude null values for it to be accurate.

Here's what I'm using for the KPI:

sum(if(aggr(sum({<[Year]={$(=max(Year)-1)}>} IF(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT)),[Cust Name])=0,0,IF(STATUS=31,-1*EXTSHIPAMT,EXTSHIPAMT)))

My problem with this is that I'm getting a value for all others. Is there a way to exclude all of the null values?

[Gysbert_Wassenaar · Accepted solution]

If would try to avoid a calculated dimension. I'd try adding this set modifier to the chart expression:

[Cust Name]=E({<[Year]={"<$(=Max(Year))"},EXTSHIPAMT={">0"}>})>}[Cust Name])

The above set modifier will exclude all customers that have records for previous years with EXTSHIPAMT value larger than zero.

[#6]

Thanks for the help, that's what I ended up doing to get it to work.

[Gysbert_Wassenaar]

Glad it worked. Can you mark the discussion as answered? That may help other people searching for a solution to a similar problem.