Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nikonmike
New Contributor II

Set Analysis - dimension less than another dimension

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.


Tags (1)
1 Solution

Accepted Solutions
ychaitanya
Contributor III

Re: Set Analysis - dimension less than another dimension

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,

5 Replies
YoussefBelloum
Esteemed Contributor

Re: Set Analysis - dimension less than another dimension

Hi,

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

nikonmike
New Contributor II

Re: Set Analysis - dimension less than another dimension

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

YoussefBelloum
Esteemed Contributor

Re: Set Analysis - dimension less than another dimension

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


rptColumnPeriod, FY, Last Year, Period

ychaitanya
Contributor III

Re: Set Analysis - dimension less than another dimension

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
New Contributor II

Re: Set Analysis - dimension less than another dimension

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