topic Re: Set analysis error in QlikView

Set analysis error

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

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

Re: Set analysis error

tresB — Thu, 17 May 2018 04:40:55 GMT

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)) "}

Re: Set analysis error

shiveshsingh — Thu, 17 May 2018 04:45:17 GMT

What's the format of your Calendar Date?

Re: Set analysis error

nareshthavidishetty — Thu, 17 May 2018 05:31:39 GMT

'DD-MMM-YYYY' format

Re: Set analysis error

Anonymous — Thu, 17 May 2018 05:58:28 GMT

try below

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

Re: Set analysis error

nareshthavidishetty — Thu, 17 May 2018 06:48:28 GMT

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,

Re: Set analysis error

shiveshsingh — Thu, 17 May 2018 07:33:14 GMT

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

Re: Set analysis error

susovan — Thu, 17 May 2018 11:28:58 GMT

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