Set Analysis with more than one condition using the same field and filters associated with the field

[thedalyfella]

Hi ,

I'm using set analysis and need some help the expression I'm using is 

(Sum({<month_number = {1}>} trans_amount) + Sum({<month_number = {2}>} trans_amount)) - Sum(trans_amount)

The trans_amount field contains transaction amounts and month number and year field dimensions are each associated a filter

if I select 2017 in the year filter and months 1 and 2 in the month filter I should get 0 in each cell of the pivot table I am using is this formula correct or is it missing any elements

Regards

Mark

[arvind_patil]

Hi,

You can use it as follows as simple:

Sum({<month_number- = {"1","2"}>}trans_amount)

Thanks & Regards,
Arvind Patil

[agigliotti]

what do you get instead of 0 as result ?

[thedalyfella]

Thanks Arvind for the reply 🙂

That's moving me in the right direction 

I tested it out and when I select 2017 in the filter associated with trans_amount i get 0 for each trans_amount in the my pivot table but when I select 2018 in the filter I still get 0 for each trans_amount I should really get negative numbers as there are values in the first two months of 2017 but no values in the first two months of 2018 

The expression I'm using is 

if($(vCurrent_Month)=2,
Sum({<month_number = {1,2}>}trans_amount) -

Sum({<year_end = {2017}>} {<month_number = {1,2}>}trans_amount),

Regards

Mark

[arvind_patil]

Dear Mark,

Kindly share sample table and expected output for better help.
Thanks & Regards,
Arvind Patil

[thedalyfella · Accepted solution]

Hi Arvind,

I had another look my expression and figured it out

if($(vCurrent_Month)=2,

Sum({$<month_number = {1}>}trans_amount) - Sum({<year_end = {$(vTwo_Years_Ago)},month_number = {1,2}>} trans_amount),  

two conditions are inside the one pair of {< >}

{<year_end = {$(vTwo_Years_Ago)},month_number = {1,2}>} two conditions are inside the one pair of {< >}

as apposed to each condition in its own {< >}

{<year_end = {$(vTwo_Years_Ago)}} {<month_number = {1,2}>}

Thanks Again Arvind for your help, you got me thinking in the right direction

All the Best

Mark