topic Fiscal Year Month - 1 in QlikView

Fiscal Year Month - 1

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

I have an alert and what I am trying to do is show Fiscal Year Month as the previous month.

2016-03 should appear as 2016-02.

sum(
if(
aggr(sum({<[Control Description]={"Policy New Business, Policy Cancellations"},
[Fiscal Year Month] = {"2016-02" }>}
[Record Count]),
Country)=0,
1,0)) > 0

above is the alert I am using.

My alert should return ONLY countries that have a record count of zero.

Any help appreciated

Re: Fiscal Year Month - 1

sunny_talwar — Wed, 16 Mar 2016 15:17:02 GMT

You want to make the Fiscal Year Month dynamic? Is that what you goal is? Also please fix your set analysis for Control Description

Sum(If(Aggr(Sum({<[Control Description] = {'Policy New Business', 'Policy Cancellations'}, [Fiscal Year Month] = {"2016-02" }>} [Record Count]), Country) = 0, 1, 0))

Re: Fiscal Year Month - 1

bobbydave — Wed, 16 Mar 2016 15:32:04 GMT

You've corrected my expression but its within the set analysis which is my issue.

I don't want to have 2016-02 showing.

I want it to be dynamic. I would like to create something that picks up Fiscal Year Month which is currently 2016-03 and brings it back to Feb (2016-02) using 'expression -1.

I've tried using

sum.....

if(....

[Fiscal Year Month] = {$(monthstart(month([Fiscal Year Month]), -1))),

.......

1,0)

Re: Fiscal Year Month - 1

sunny_talwar — Wed, 16 Mar 2016 15:35:03 GMT

May be something like this:

Sum(If(Aggr(Sum({<[Control Description] = {'Policy New Business', 'Policy Cancellations'}, [Fiscal Year Month] = {"$(=Date(AddMonths(Max([Fiscal Year Month]), -1), 'YYYY-MM'))"}>} [Record Count]), Country) = 0, 1, 0))