topic Re: YTD previous month in QlikView

YTD previous month

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have built a stiaght table with values for this years months against customer.

The report is run on an ad hoc basis so I only return a grand for the completed previous months. For example today is the 28th April so the grand total should provide a sum of January to March. I thought this formula would do it, but I'm obviously missing something

Sum({$<
[LinkId] = {"FIN"},
[Bill Code]-={"REBATE"},
[Bill Country Code]-={"US","MY","MX","AU","PE"},
[Bill Date]={">=$(=yearstart(today()),=monthend(Addmonths(Today(), -1)) "}
>}[Bill Value])

I've split the Bill Date expression out and (=yearstart(today()) retruns 01/01/2017 and monthend(Addmonths(Today(), -1)) returns 31/03/2017.

Both are the dates I want to query between. Any help would be greatly appeciated

Thanks in advance

Dave

Re: YTD previous month

tresB — Fri, 28 Apr 2017 10:24:38 GMT

May be like:

[Bill Date]={">=$(=yearstart(today())) <=$(=monthend(Addmonths(Today(), -1))) "}

Re: YTD previous month

Anonymous — Fri, 28 Apr 2017 10:52:26 GMT

Follow this link. It should help you a lot.

QlikView App: Set Analysis - Prior Period Compa... | Qlik Community

Re: YTD previous month

Fri, 28 Apr 2017 11:07:11 GMT

unfortunately this doesn't work. It still bring back 0s. I can get round the problem using and if expression and summing the previous expressions.

I guess it's a workaround

Re: YTD previous month

tresB — Fri, 28 Apr 2017 11:32:16 GMT

Could you post a representative sample?

Re: YTD previous month

Fri, 28 Apr 2017 11:35:15 GMT

unfortunately not, data protection wont allow us to even using the scrambler