topic Re: Set Analysis in QlikView

Set Analysis

Tue, 21 Apr 2015 17:24:45 GMT

Hi,

Can somebody help me with this set analysis expression?

=sum({<[Transaction Code]={'959','654','661','660','659','658','657','655','664','653',

                           '652','651','650','656','662','663','851','852','853','854',

                           '855','856','857','858','859','860','861','862'},

        [Transaction Run Month Year]={'Mar 2015'}>}[Transaction Amount])

Instead of hard coding "Mar 2015", I want the expression to automatically take Transaction Run Month Year -1.

Thank you in advance.

Regards,

Adi

Re: Set Analysis

MK_QSL — Tue, 21 Apr 2015 17:39:35 GMT

{"$(=Date(MonthStart(Today(),-1),'MMM-YYYY'))"}

Re: Set Analysis

Tue, 21 Apr 2015 17:42:59 GMT

Hi ,

Create a variable let vMaxMinus1Year= AddYears(Max(Transaction Date()),-1);

Then use the above variable in set analysis

sum({<[Transaction Code]={'959','654','661','660','659','658','657','655','664','653',

                           '652','651','650','656','662','663','851','852','853','854',

                           '855','856','857','858','859','860','861','862'},

        [Transaction Run Month Year]={'$(vMaxMinus1Yea)'}>}[Transaction Amount])

Thanks,

shekar.

Re: Set Analysis

Tue, 21 Apr 2015 18:23:01 GMT

It's not working Manish.

Re: Set Analysis

Tue, 21 Apr 2015 18:28:56 GMT

Its not working, Shekar!!!

Re: Set Analysis

Anonymous — Tue, 21 Apr 2015 18:34:12 GMT

Remove "-" from the Manish's solution:

... [Transaction Run Month Year]={"$(=Date(MonthStart(Today(),-1),'MMM YYYY'))"} ...

Re: Set Analysis

Tue, 21 Apr 2015 18:39:40 GMT

Thank you Michael, it worked.

Re: Set Analysis

Anonymous — Tue, 21 Apr 2015 18:45:43 GMT

I think Manish deserves the "correct" points.

Re: Set Analysis

Tue, 21 Apr 2015 18:48:15 GMT

Thank u Manish!!