Aggr Function with If Statement

[#1]

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

[Anonymous]

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]

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)

[#5]

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?

[#7]

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

[#8]

~

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]

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