topic Re: Set Analysis with more than one condition using the same field and filters associated with the f in App Development

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

thedalyfella — Wed, 09 Jan 2019 13:11:32 GMT

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

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

arvind_patil — Wed, 09 Jan 2019 13:28:19 GMT

Hi,

You can use it as follows as simple:

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

Thanks & Regards,
Arvind Patil

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

agigliotti — Wed, 09 Jan 2019 13:42:57 GMT

what do you get instead of 0 as result ?

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

thedalyfella — Wed, 09 Jan 2019 16:33:21 GMT

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

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

arvind_patil — Thu, 10 Jan 2019 08:06:30 GMT

Dear Mark,

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

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

thedalyfella — Thu, 10 Jan 2019 09:29:11 GMT

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