Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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