Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Fiscal Year Month - 1

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

3 Replies
sunny_talwar

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

bobbydave
Creator III
Creator III
Author

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)

sunny_talwar

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