YTD previous month

[#1]

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

[tresesco]

May be like:

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

[Anonymous]

Follow this link. It should help you a lot.

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

[#4]

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

[tresesco]

Could you post a representative sample?

[#6]

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