MTD VIA SET ANALYSIS

[#1]

Hi Team,

My Data look like this---

Name	Date	Amount
aa	23-01-2015	1119
aa	11-04-2015	1197
aa	28-06-2015	1275
aa	14-09-2015	1353
aa	01-12-2015	1431
aa	17-02-2016	1509
aa	05-05-2016	1587
aa	22-07-2016	1665
aa	08-10-2016	1743
aa	30-11-2015	1300

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]

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]

Hi

Use this:

=firstsortedvalue(Date, -Date, 2)

ie like this:

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

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

HTH

Jonathan

[jvishnuram]

Hi Sumit,

Try this

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

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

        Quarter = ,

        Month = >} Sales)