Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
DaithiOK
Contributor II
Contributor II

How to write an if statement with nested aggr functions

I am having real trouble with the syntax of my formula. Currently in a table i have this:

DaithiOK_0-1592305894558.png

whereby i have a list of projects and the number of days since their last steering status. 0 is a default for never having had one before. 

The # Projects column is my expression to limit the contents of the table and the formula is this:

if(DaysSinceLast_SteeringStatusT =0 or DaysSinceLast_SteeringStatusT >45,
Count(aggr(COUNT(
		{<
			[L2 Programme Type Code Parent] = {'xyz'}, 
			[Year] = {$(v_Max_Year)},
			ReportMonth={$(vCurrentMonth)},
			DaysSinceLast_SteeringStatusT = {">45"},
		>} 
distinct [Project Code])
,Programme))

+
Count(aggr(COUNT(
		{<
			[L2 Programme Type Code Parent] = {'xyz'}, 
			[Year] = {$(v_Max_Year)},
			ReportMonth={$(vCurrentMonth)},
			DaysSinceLast_SteeringStatusT = {0},
		>} 
distinct [Project Code])
,Programme)))

I have to aggr the count because i am rolling up child projects of xyz to L2 and 'Programme' is the level i aggregate at. I also have to include the IF statement because there are some stragglers left behind in the rollup when i use DaysSInceLast_SteeringStatusT. 

 

What I am trying to do though is to put the number 41 that you see in the table into a KPI (text box) but I can't figure out how to as I cannot use the IF statement

if(DaysSinceLast_SteeringStatusT =0 or DaysSinceLast_SteeringStatusT >45,....

as it doesnt relate to anything like in the table. 

 

I tried doubling up on the aggr functions outside of the IF statement (below) but it doesnt work.

=
count(
{<

	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {">45"}
	>}
aggr(
if( DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT >45,

Count(aggr((COUNT(
			{<
				[L2 Programme Type Code Parent] = {'xyz'}, 
				[Year] = {$(v_Max_Year)},
				ReportMonth={$(vCurrentMonth)},
				DaysSinceLast_SteeringStatusT = {">45"}	
			>} 
	distinct [Project Code]))
	)
,Programme))
,Programme)
distinct [Project Code])

 

Any help translating the table formula into a formula that works in a text box would be great I'm struggling to get to grips wtih aggrs. 

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

May be you need this

Count(Aggr(
If(DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT > 45,
Count({<
	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {">45"}>} 
DISTINCT [Project Code])
Count({<
	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {0}>} 
DISTINCT [Project Code]))
, Programme))

But I feel like you really need a Sum for the outer aggregation like this

Sum(Aggr(
If(DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT > 45,
Count({<
	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {">45"}>} 
DISTINCT [Project Code])
Count({<
	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {0}>} 
DISTINCT [Project Code]))
, Programme))

View solution in original post

1 Reply
sunny_talwar

May be you need this

Count(Aggr(
If(DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT > 45,
Count({<
	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {">45"}>} 
DISTINCT [Project Code])
Count({<
	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {0}>} 
DISTINCT [Project Code]))
, Programme))

But I feel like you really need a Sum for the outer aggregation like this

Sum(Aggr(
If(DaysSinceLast_SteeringStatusT = 0 or DaysSinceLast_SteeringStatusT > 45,
Count({<
	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {">45"}>} 
DISTINCT [Project Code])
Count({<
	[L2 Programme Type Code Parent] = {'xyz'}, 
	[Year] = {$(v_Max_Year)},
	ReportMonth={$(vCurrentMonth)},
	DaysSinceLast_SteeringStatusT = {0}>} 
DISTINCT [Project Code]))
, Programme))

View solution in original post