Set analysis error

[nareshthavidishetty]

HI,

We have used the below logic to derive previous month sales.

But I'm gettion zero sales when executin the logic.

Logic used: =

num(sum({$<[Month Abbr]=, Year=, [Quarter Name]=, [Calendar Date]={"$(=AddMonths(MonthEnd(Max([Calendar Date])),-1))"}>}[# QN Open]),'#,##0')

Note: The data is available on every monthend

Thanks

[tresesco]

I guess you are missing giving a range of date. It should be something like:

[Calendar Date]={">=$(=MonthStart(Max([Calendar Date]),-1)) <=$(=MonthEnd(Max([Calendar Date]),-1)) "}

[shiveshsingh]

What's the format of your Calendar Date?

[nareshthavidishetty]

'DD-MMM-YYYY' format

'

[Anonymous]

Hi

try below

=num(sum({$<[Calendar Date]={">=$(=AddMonths(MonthEnd(Max([Calendar Date])),-1))<=$(=max([Calendar Date]))"}>}[# QN Open]),'#,##0')

[nareshthavidishetty]

HI,

I'm trying the below expression.But I'm getting issue as error in set expression.

logic : =num(sum({<[Month Abbr]=,Year=,[Quarter Name]=,[Calendar Date]={">=$(=AddMonths(date(MonthStart(Max([Calendar Date])),'DD-MMM-YYYY'),-1))<=$(=AddMonths(date(MonthEnd(Max([Calendar Date])),'DD-MMM-YYYY'),-1) "}>}[# QN Open]),'#,##.0')

Thanks,

[shiveshsingh]

Try this once..

=num(sum({<[Month Abbr]=,Year=,[Quarter Name]=,[Calendar Date]={">=$(=AddMonths(date(MonthStart(Max([Calendar Date])),'DD-MMM-YYYY'),-1))<=$(=AddMonths(date(MonthEnd(Max([Calendar Date])),'DD-MMM-YYYY'),-1))"}>}[# QN Open]),'#,##.0')

[susovan]

Hi,

Try this,

num(sum({$<[Month Abbr]=, Year=, [Quarter Name]=, [Calendar Date]={">=$(=AddMonths(MonthEnd(Max([Calendar Date])),-1)) <=$(=AddMonths(MonthEnd(Max([Calendar Date])),-1))"}>}[# QN Open]),'#,##0')