How to write an if statement with nested aggr functions

DaithiOK — Sat, 16 Nov 2024 18:29:13 GMT

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

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.

Re: How to write an if statement with nested aggr functions

sunny_talwar — Tue, 16 Jun 2020 13:33:05 GMT

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

topic How to write an if statement with nested aggr functions in QlikView

How to write an if statement with nested aggr functions

Re: How to write an if statement with nested aggr functions