Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
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))