topic Re: Help in a complicated Set Analysis expression in QlikView

Help in a complicated Set Analysis expression

Tue, 02 Feb 2016 09:26:38 GMT

Hi Community,

I need a little help in a set analysis expression. The expression is mentioned below:

=SUM({<BUDGET_DATE = {">=$(=Num((Max(BUDGET_DATE))))<=$(=DATE(MONTHSTART(Today()),'DD-MM-YYYY'))"}>}BUDGET_BALANCE)

The budget_date has 12 dates (12 month ends of 2016). This expression returns me January 2016 (balances). I want February 2016.

What I have tried so far is:

=SUM({<BUDGET_DATE ={">=$(>=Num((Max(BUDGET_DATE))))<=$(=DATE(MONTHEND(Today())+1,'DD-MM-YYYY'))"}>}BUDGET_BALANCE)

The result I get with the above expression accumulates January and February, though I only want February. Moreover please also mention how should I go to next month date as well. i.e March 2016.

Thank You

Usama

Re: Help in a complicated Set Analysis expression

sunny_talwar — Tue, 02 Feb 2016 10:33:46 GMT

May be this:

=Sum({<BUDGET_DATE = {">=$(=Date(AddMonths(MonthStart(Today()), 1),'DD-MM-YYYY'))<=$(=Date(AddMonths(MonthEnd(Today()), 1),'DD-MM-YYYY'))"}>}BUDGET_BALANCE)

Re: Help in a complicated Set Analysis expression

Anonymous — Tue, 02 Feb 2016 10:35:35 GMT

try this?

=SUM({<BUDGET_DATE= ,

BUDGET_DATE = {'>=$(=date(Max(BUDGET_DATE)),'DD-MM-YYYY')<=$(=DATE(MONTHSTART(Today()),'DD-MM-YYYY'))'}>} BUDGET_BALANCE)

Also make sure Format of BUDGET_DATE should be 'DD-MM-YYYY'

Re: Help in a complicated Set Analysis expression

Wed, 03 Feb 2016 11:19:54 GMT

Thank you Sunny and Balraj for your help.

=Sum({<BUDGET_DATE = {">=$(=Date(MonthStart(Today()),'DD-MM-YYYY'))<=$(=Date(MonthEnd(Today()),'DD-MM-YYYY'))"}>}BUDGET_BALANCE) worked out for me.

Regards,

Usama

Re: Help in a complicated Set Analysis expression

sunny_talwar — Wed, 03 Feb 2016 17:45:09 GMT

Awesome, I am glad we were able to help.

Please close this thread by marking the correct and helpful answers.

Best,

Sunny