Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator III

Set analysis error

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

7 Replies
tresesco
MVP
MVP

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
Master
Master

What's the format of your Calendar Date?

nareshthavidishetty
Creator III
Creator III
Author

'DD-MMM-YYYY' format

'

Anonymous
Not applicable

Hi

try below

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

nareshthavidishetty
Creator III
Creator III
Author

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
Master
Master

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
Partner - Specialist
Partner - Specialist

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')

Warm Regards,
Susovan