topic Re: Previous Year in Set Analysis calculation in QlikView

Previous Year in Set Analysis calculation

ngulliver — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I currently have an expression:

=num(sum({<YearMonth={"$(vMaxYearMonth)"},KPI = {[Overheads]}>} Amount),'#,##0')

where $(vMaxYearMonth is being used to limit the month to no greater than today:

=Date(addmonths(max({<[YearMonth]*={'<=$(=Date(today(), 'YYYYMM'))'}>}[YearMonth]),0), 'YYYYMM')

I am trying to create a similar expression to view the same month for the previous year. However, every attempt I have made has not worked. I believe I should be using AddMonths function but I cannot seem to get it to work within this expression.

Can anyone clarify where I would put it in this expression ?

Regards,

Neil

Re: Previous Year in Set Analysis calculation

sunny_talwar — Fri, 01 Jun 2018 13:56:56 GMT

May be try this

=Num(Sum({<YearMonth = {"$(=Date(MonthStart(Today(), -1), 'YYYYMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')

Re: Previous Year in Set Analysis calculation

ngulliver — Fri, 01 Jun 2018 14:15:50 GMT

Hi, Sunny.

Thanks for your quick response.

I can see how that would work but I am trying to make the calculation react according to dates selected (hence the vMaxYearMonth). So if February 2018 is selected, it will show February 2017.

Re: Previous Year in Set Analysis calculation

sunny_talwar — Fri, 01 Jun 2018 14:19:36 GMT

Then may be this

=Num(Sum({<YearMonth = {"$(=Date(MonthStart(Max(YearMonth), -1), 'YYYYMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')

Re: Previous Year in Set Analysis calculation

prat1507 — Fri, 01 Jun 2018 14:25:47 GMT

You should use =Date(AddYears(today(),-1))

Re: Previous Year in Set Analysis calculation

prat1507 — Fri, 01 Jun 2018 14:33:32 GMT

Hi probably this

=Num(Sum({<YearMonth = {"$(=date(AddYears(Date#(YearMonth ,'YYYYMMM'),-1),'YYYYMMM'))"}, KPI = {[Overheads]}>} Amount),'#,##0')