topic Re: Quarters sum up in QlikView

Quarters sum up

kkkumar82 — Wed, 23 Mar 2016 13:17:39 GMT

All,

I have a requirement to be accomplished.

Lets say I am in Q1 2016 (as we are in March), based on the quarter

if we are in Q1 I want to add up Q2 + Q3 + Q4 Amount

if we are in Q2 I want to add up Q3 + Q4 Amount

if we are in Q3 I want to add up Q4 Amount

if we are in Q4 I want to show 0

In the attached qvw in second sheet I tried a logic for achieving this using some if stuff, I have also done the same on months ,

I just wanna know any better logic than this.

If so please add to this.

Thanks

Kiran Kumar

sunny_talwar — Wed, 23 Mar 2016 13:28:13 GMT

May be this as an alternative:

=Sum({<Year = {$(=Year(today()))}, QuarterNum = {"$(='>' & Ceil(Month(Today())/3))"}, Date, Quarter, Month>} Amount)

Where QuarterNum is created in Script like this:

ceil(Month(Date($(vDate) + Rowno() -1 ) )/3) as QuarterNum,

Wed, 23 Mar 2016 13:49:21 GMT

Try this one,

=Sum({<Year = {$(=Year(today()))},MonthNum = {">=$(=Num(Month(QuarterStart(Today()))) + 3) <=$(=num(month(Yearend(today()))))"}>}Amount)

kkkumar82 — Wed, 23 Mar 2016 15:51:55 GMT

Thanks Sunny and Sindhu for your responses.

I think sindhu your expression will increase one more calculation after Quarterstart i.e., +3 .

Sunny your expression is fine and I think it improves performance as I can avoid certain if conditions.

one question here if we are in Nov 2016 will your expression return 0.

Regards

Kiran Kumar

sunny_talwar — Wed, 23 Mar 2016 15:52:38 GMT

Yes it will return 0 when you are in Nov 2016 since its Q4