topic Re: Aggr Function with If Statement in App Development

Aggr Function with If Statement

Tue, 28 Mar 2017 10:06:58 GMT

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

Anonymous — Tue, 28 Mar 2017 10:25:13 GMT

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

karthiksrqv — Tue, 28 Mar 2017 10:27:55 GMT

Try this in your KPI object,

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

Re: Aggr Function with If Statement

sunny_talwar — Tue, 28 Mar 2017 11:15:43 GMT

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

Tue, 28 Mar 2017 12:27:19 GMT

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

sunny_talwar — Tue, 28 Mar 2017 12:28:25 GMT

Can you share a sample or post some screenshots?

Re: Aggr Function with If Statement

Tue, 28 Mar 2017 12:30:31 GMT

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

Tue, 28 Mar 2017 12:38:31 GMT

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

Anonymous — Tue, 28 Mar 2017 13:26:38 GMT

Can you post a sample qvf??

Re: Aggr Function with If Statement

sunny_talwar — Tue, 28 Mar 2017 13:33:29 GMT

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

Tue, 28 Mar 2017 14:07:58 GMT

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