10 Replies Latest reply: Mar 28, 2017 10:07 AM by Tanguy de Becdelievre

# 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

• ###### Re: Aggr Function with If Statement

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.

• ###### Re: Aggr Function with If Statement

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

• ###### Re: Aggr Function with If Statement

Try this in your KPI object,

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

• ###### Re: Aggr Function with If Statement

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)

• ###### Re: Aggr Function with If Statement

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

• ###### Re: Aggr Function with If Statement

Can you share a sample or post some screenshots?

• ###### Re: Aggr Function with If Statement

~

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,

• ###### Re: Aggr Function with If Statement

Can you post a sample qvf??

• ###### Re: Aggr Function with If Statement

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

• ###### Re: Aggr Function with If Statement

Thanks a lot it really helped :

The correct formula was :

Sum(

{<[MONTH FULL NAME]=>}

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