topic Re: Addmonths in Set Analysis ( next 3 months) in QlikView

Addmonths in Set Analysis ( next 3 months)

Thu, 11 Jun 2015 07:40:10 GMT

Hello,

Guys need your help!

I want to create an expression with average of next three months. I tried to use this one with addmonths function, but it's not working

Avg({<Month_name={">=$(=Date(addmonths(Month_name,1),'MMM'))<=$(=Date(addmonths(Month_name,3),'MMM'))"}>} Values)

Please help!

Re: Addmonths in Set Analysis ( next 3 months)

swuehl — Thu, 11 Jun 2015 07:43:44 GMT

You're applying a numeric search in your element list. Does field Month_name have a numeric representation (is it a dual value with a text and a numeric value, the numeric value showing monthstart date?

Maybe this is of some help:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

Re: Addmonths in Set Analysis ( next 3 months)

PrashantSangle — Thu, 11 Jun 2015 07:49:23 GMT

Hi,

How did you create Month_name field or it is already availble in script???

If you are creating Month_name using Month(datefield)

then try

Avg({<datefield={">=$(=Date(addmonths(datefield,1)))<=$(=Date(addmonths(datefield,3)))"},Month_name=>} Values)

Regards

Re: Addmonths in Set Analysis ( next 3 months)

Thu, 11 Jun 2015 08:10:28 GMT

Hi,

i tried this one Avg({$<Month_Date={">=$(=addmonths(Month_Date,1))<=$(=addmonths(Month_Date,3))"},Month_Date=>}Bench_Data)

and it worked but incorrectly.

The thing is the user picks several months in the filter like : from Jan till Jun, from Jan till July etc. And I want the table to show the average of next 3 months.

For example, if the user selects the months from January till June, I want it to show the average of values starting from July till September.

Re: Addmonths in Set Analysis ( next 3 months)

Thu, 11 Jun 2015 08:15:11 GMT

Month_name field was created in the script

Re: Addmonths in Set Analysis ( next 3 months)

antoniotiman — Thu, 11 Jun 2015 08:16:07 GMT

Hi Diana,

add Max() in AddMonths

AddMonths(Max(Month_Date),1) and ,3 (maybe 4)

and remove Month_Date= from set analysis

Regards,

Antonio

Re: Addmonths in Set Analysis ( next 3 months)

PrashantSangle — Thu, 11 Jun 2015 09:23:30 GMT

Hi,

Use max() to get max of selected months

try like

Avg({$<Month_Date={">=$(=addmonths(max(Month_Date),1))<=$(=addmonths(max(Month_Date),3))"},Month_Date=>}Bench_Data)

Regards

Re: Addmonths in Set Analysis ( next 3 months)

Thu, 11 Jun 2015 10:06:33 GMT

It doesnt seem to work correctly, I checked again, and it gives me the wrong result. It seems to get the average of the max month. Like if you choose June for example, it gives you the average of previous months, from January till June... This is odd

Re: Addmonths in Set Analysis ( next 3 months)

PrashantSangle — Thu, 11 Jun 2015 10:14:39 GMT

Hi,

Is it possible to share app???

debug your code by remove label name of expression then on front end put pointer on that expression,

You will get value in set expression.

Just check it ,what it is showing???

And possible post screen shot of it

Regards

Re: Addmonths in Set Analysis ( next 3 months)

Mon, 15 Jun 2015 06:34:01 GMT

Max,

Sorry, I dont understand how to make all these corrections.

Here is my sample app, would be greateful for help.

Thank You! ! !

Re: Addmonths in Set Analysis ( next 3 months)

PrashantSangle — Mon, 15 Jun 2015 07:02:50 GMT

Hi,

Try below expression

=Avg({<Month_Date={">=$(=Date(addmonths(Month_Date,1),'MMM-YY'))<=$(=Date(addmonths(Month_Date,3),'MMM-YY'))"},Month_name=>} Bench_Data)

Regards

Re: Addmonths in Set Analysis ( next 3 months)

Mon, 15 Jun 2015 07:47:57 GMT

This expression isn't working

Re: Addmonths in Set Analysis ( next 3 months)

PrashantSangle — Mon, 15 Jun 2015 07:59:57 GMT

Hi,

I have tested data let me know what was your expecting output.

It is working at my end.

testCase:

Selected Month : Mar

Employee Name : Itojqj Tygnss

Expected Avg for next 3 month is sum(Apr,May,Jun)/3

Result :

Then Bench_data for Apr,May jun is 1.94

and Avg is 0.64666

Please find attached apps.

Regards,

Re: Addmonths in Set Analysis ( next 3 months)

Mon, 15 Jun 2015 08:20:07 GMT

Wow, it works now.

Thanks a lot for your hep!!!