Set Analysis with Addmonths

[#1]

Hi All,

I want below expression in set analysis

=Sum(if(YearMonth>=Addmonths(CommonMonth,-2) and YearMonth<=Addmonths(CommonMonth,0),Sales))

Pls find the Sample app attached for reference.

Thanks in advance,

Regards,

Bhaskar

[lironbaram]

hei

why do you want the if clause in set analsys

you shuld know that in the set analsys

the  calculation of the condition is out side the table, which means the same result for all the table

i think it's not what you want

[#3]

Hey,

I dont want IF clause in Set Analysis.

I want to replace IF Clause with set analysis

I want the  same OutPut with Set Analysis  which  i am getting using IF Clause .

I have tried writing expreeion using Set Analysis but  the Expression is working only when i Select a CommonMonth.

Please refer my attachment .

[martin59]

Hi,

You have to do this :

Sum({<YearMonth={">=$(=AddMonths(CommonMonth,-2) <=$(=Only(CommonMonth))"}>} Sales)

Hope that helps you

Martin

[lironbaram]

hei

that what i mean

the field Commonmonth in the dimension get a lot of values

a set analsys is for one value

when you use set analsys the condition is calculated out side the table

which mean the condition sees all the commonmonth values  and cant calculate it

[#6]

HI Martin,

This is not working.

I am using common calendar.

[martin59]

So, you have to do that and you'll work on the max date (selections or not) :

Sum({<YearMonth={">=$(=AddMonths(Max(CommonMonth),-2) <=$(=Max(CommonMonth))"}>} Sales)

Hope that helps you

Martin

[spsrk_84]

Hi Bhaskar,

I think the result is not coming due to the date format in your expression .i.e the date formats are different when u use the addmonths functions... i changed them and used variables i am getting the result what is expected..

Please find the attached file

Regards,

Shiva

[#9]

Hi Martin,

Thanks  for ur quick reply.

Still this is not working ...

you can try on sample app which i had attached.

[#10]

Dear Shiva ,

Thanks For ur Reply..

I Know it is working  when I select a CommonMonth,But  I want this even when i dont select any thing in common month.

Which i am able to achieve using if condition.

Please check my First Charts Expression, which i have return with IF Condition.