Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
Hi,
would you be able to share your table structure/model/sample app/sample data ?
Not sure how to easily do that. It has approx 21 different tables and about 15 million records.
try to attach some lines/Image of these fields (to see the values and the data structure)
rptColumnPeriod, FY, Last Year, Period
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,
That worked! Awesome, thanks! I have run into this before and now I know why it didn't work.