Set Analysis - dimension less than another dimension

[nikonmike]

I have sales data for multiple years and I am trying to build a P&L type format with current year in one column and prior year in another.  For the current year, I only have sales up through the current period, but for last year I have a full year worth of sales.  So for last year's sales, I am trying to limit them to only go through the current period.

Here is the portion of the formula I am using for the last year's sales:

if(rptColumnPeriod ='Last Year', Sum({<Account2={'Food Sales'}, Account1=,  FY=P([Last Year]), Period={"<=CP"}>} Amount)*-1,

Period is dimension for our operating periods (1-13).  The table for sales includes this field.

CP is the a dimension for the current operating period.

If I remove the Period={"<=CP"} portion of the formula, I get the full years sales.  If I include it, I am getting 0 sales.

[YoussefBelloum]

Hi,

would you be able to share your table structure/model/sample app/sample data ?

[nikonmike]

Not sure how to easily do that.  It has approx 21 different tables and about 15 million records.

[YoussefBelloum]

try to attach some lines/Image of these fields (to see the values and the data structure)

rptColumnPeriod, FY, Last Year, Period

[ychaitanya · Accepted solution]

CP is the field name , if you try to use like below in SET analysis , it doesn;t interpret as field and treats as some text.

So it's resulting to 0.

if(rptColumnPeriod ='Last Year', Sum({<Account2={'Food Sales'}, Account1=,  FY=P([Last Year]), Period={"<=CP"}>} Amount)*-1,

try something like this..

if(rptColumnPeriod ='Last Year', Sum({<Account2={'Food Sales'}, Account1=,  FY=P([Last Year]),

Period={"$(='>=' & CP)"}

>}

Amount)*-1,

[nikonmike]

That worked!  Awesome, thanks!  I have run into this before and now I know why it didn't work.