topic Re: Set Analysis with Addmonths in QlikView

Set Analysis with Addmonths

Wed, 20 Jul 2011 07:21:41 GMT

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

Set Analysis with Addmonths

lironbaram — Wed, 20 Jul 2011 07:39:54 GMT

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

Set Analysis with Addmonths

Wed, 20 Jul 2011 08:43:51 GMT

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 .

Re: Set Analysis with Addmonths

martin59 — Wed, 20 Jul 2011 08:48:07 GMT

Hi,

You have to do this :

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

Hope that helps you

Martin

Set Analysis with Addmonths

lironbaram — Wed, 20 Jul 2011 08:55:49 GMT

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

Set Analysis with Addmonths

Wed, 20 Jul 2011 08:57:16 GMT

HI Martin,

This is not working.

I am using common calendar.

Re: Set Analysis with Addmonths

martin59 — Wed, 20 Jul 2011 09:46:01 GMT

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

Re: Set Analysis with Addmonths

spsrk_84 — Wed, 20 Jul 2011 11:16:07 GMT

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

Set Analysis with Addmonths

Wed, 20 Jul 2011 12:24:34 GMT

Hi Martin,

Thanks for ur quick reply.

Still this is not working ...

you can try on sample app which i had attached.

Set Analysis with Addmonths

Wed, 20 Jul 2011 12:38:50 GMT

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.

Re: Set Analysis with Addmonths

martin59 — Wed, 20 Jul 2011 12:44:15 GMT

Sorry I didn't see you document.

Look at the attachment, you can use the cumulative option.

Hope that helps you

Martin

Re: Set Analysis with Addmonths

rmuhammad — Tue, 10 Oct 2017 13:59:58 GMT

please mark the correct answer so it helps others.