Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Not applicable

Help in a complicated Set Analysis expression

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

4 Replies

Re: Help in a complicated Set Analysis expression

May be this:

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

balrajahlawat
Esteemed Contributor

Re: Help in a complicated Set Analysis expression

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'

Not applicable

Re: Help in a complicated Set Analysis expression

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

Awesome, I am glad we were able to help.

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

Best,

Sunny