Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Showing results for 
Search instead for 
Did you mean: 
newuser
Creator II
Creator II

Measure Expression Not Limiting To Positives In Pivot Table

In a pivot table, the following expression is not limiting to positive values. Any idea why? The plain text is also below; screenshot with cleaner formatting follows. The pivot table column is Year_Placed. The rows are a different (higher-level) field.

sum( aggr(if(

(sum( if( Month_Placed <= CurrMonth /*and Year_Placed = CurrYr*/ and placedt_all=aggr( nodistinct
max( if(Month_Placed <= CurrMonth /*and Year_Placed = CurrYr */, placedt_all)),acctpol, Year_Placed), Placed))
+
sum( if( Month_Placed <= CurrMonth /*and Year_Placed = CurrYr*/
and placedt_all=aggr($ nodistinct
max( if( Month_Placed <= CurrMonth /*and Year_Placed = CurrYr*/, placedt_all)),acctpol, Year_Placed) , Adjustment))) > 0,

sum( if(Month_Placed <= CurrMonth /*and Year_Placed = CurrYr*/ and placedt_all=aggr( nodistinct
max( if( Month_Placed <= CurrMonth /*and Year_Placed = CurrYr*/, placedt_all)),acctpol, Year_Placed), Placed))
+
sum( if( Month_Placed <= CurrMonth /*and Year_Placed = CurrYr*/
and placedt_all=aggr( nodistinct
max( if( Month_Placed <= CurrMonth /* and Year_Placed = CurrYr*/ , placedt_all)),acctpol, Year_Placed) , Adjustment)), 0), acctpol, Year_Placed))

2022-05-23_14-17-55.jpg

Labels (1)
1 Reply
newuser
Creator II
Creator II
Author

I think I figured it out. The AGGR "group by" fields needed another field (one of the higher-level fields I was using as my pivot table row dimension). Once I added that higher-level field, the positive value criteria in the IF statement worked.