topic Re: MTD VIA SET ANALYSIS in QlikView

MTD VIA SET ANALYSIS

Thu, 30 Jan 2014 12:31:21 GMT

Hi Team,

My Data look like this---

Here my max year and month is 2016 and Oct, now i want calculate previous month amount of max year i.e for July month..

I have implemented the following logic but the result is zero because in MonthID the monthID is coming for sep but i want it to come for July

=Sum({$<MonthID = {$(=Max(MonthID) -1)},

Year = ,

Quarter = ,

Month = >} Sales)

shawn-qv — Thu, 30 Jan 2014 12:55:24 GMT

It makes sense that max(MonthID)-1 = Sep, because your max month is Oct. So Oct-1 will return Sep.

I think you need to define your MTD. Based on your expression, it will return the MonthID of every year because you're ignoring the Year field.

jonathandienst — Thu, 30 Jan 2014 12:56:38 GMT

Use this:

=firstsortedvalue(Date, -Date, 2)

ie like this:

=Sum({<Date = {"=$(=firstsortedvalue(Date, -Date, 2))"},

Year = , Quarter = , Month = >} Sales)

HTH

Jonathan

jvishnuram — Thu, 30 Jan 2014 13:00:30 GMT

Hi Sumit,

Try this

Sum({$<MonthID = {"$(=Max(MonthID) -1)"},

Year = {"$(=Max(Year))"},

Quarter = ,

Month = >} Sales)