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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggr Function with If Statement

Good morning,

I am having difficulties with 1 formula.

It works perfectly on a Table but when I try to put it on a KPI object, the function does not work.

The formula is the following:

if(Max({<QTYCONSUMED={"<0"}>}[MONTH ORDER])>=(vOPENBAL-12),sum(Amount))

Any idea on how to solve this issue ?

Thanks for your help,

Tanguy

10 Replies
Anonymous
Not applicable
Author

What is the dimension of the straight table?  As it is working on a straight table, it is being evaluated in the context of the Dimension like Dim1=, Dim2= etc. So in KPI object, you might not have that dimension. Try putting the filter in the set analysis. you might also share a sample .qvw if possible.

karthiksrqv
Partner - Creator II
Partner - Creator II

Try this in your KPI object,

sum(aggr(if(Max({<QTYCONSUMED={"<0"}>}[MONTH ORDER])>=(vOPENBAL-12),sum(Amount)),<dimension_used_in_straight_table)>)

sunny_talwar

Assuming you have Dim1 as your dimension in the chart where this is working, try this

Sum(Aggr(if(Max({<QTYCONSUMED={"<0"}>}[MONTH ORDER])>=(vOPENBAL-12),sum(Amount)), Dim1)

Not applicable
Author

Hi,

This function is giving a different result than in the table. There is only 1 dimension in My Table so I don't understand why...

sunny_talwar

Can you share a sample or post some screenshots?

Not applicable
Author

There is 1 dimension in my table,

the function Sum(Aggr(if(Max({<QTYCONSUMED={"<0"}>}[MONTH ORDER])>=(vOPENBAL-12),sum(Amount)), Dim1) does not give the same result

Not applicable
Author

Capture.PNG~

The function in the Graph is the following:

FMI=

if(Max({<QTYCONSUMED={"<0"},[MONTH FULL NAME]=>}[MONTH ORDER])>=(vOPENBAL-12),

Sum({<[MONTH ORDER]={"<=$(vOPENBAL)"},[MONTH FULL NAME]=,STOCKITEMCODE={"<>MANUALADJ"},[CREATION DATE]={"<=$(=Date(Today()-360))"}>}Amount))

The function in the KPI is :

Sum(AGGR(if(Max({<QTYCONSUMED={"<0"},[MONTH FULL NAME]=>}[MONTH ORDER])>=(vOPENBAL-12),

Sum({<[MONTH ORDER]={"<=$(vOPENBAL)"},[MONTH FULL NAME]=,STOCKITEMCODE={"<>MANUALADJ"},[CREATION DATE]={"<=$(=Date(Today()-360))"}>}Amount))

,STOCKITEMCODE))

Thanks,

Anonymous
Not applicable
Author

Can you post a sample qvf??

sunny_talwar

Try this out

Sum(

{<QTYCONSUMED={"<0"},[MONTH FULL NAME]=>}[MONTH ORDER])>=(vOPENBAL-12),

Sum({<[MONTH ORDER]={"<=$(vOPENBAL)"},[MONTH FULL NAME]=,STOCKITEMCODE={"<>MANUALADJ"},[CREATION DATE]={"<=$(=Date(Today()-360))"}>}

AGGR(if(Max({<QTYCONSUMED={"<0"},[MONTH FULL NAME]=>}[MONTH ORDER])>=(vOPENBAL-12),

Sum({<[MONTH ORDER]={"<=$(vOPENBAL)"},[MONTH FULL NAME]=,STOCKITEMCODE={"<>MANUALADJ"},[CREATION DATE]={"<=$(=Date(Today()-360))"}>}Amount))

,STOCKITEMCODE))