Using if statement in KPI object

[Anonymous]

Hi guys,

in a KPI object I want to show the total amount of accounts that we've prepaid.

This means, that I want to show the total sum of a measure if this is > 0.

I tried the following script:

if(SUM({<[Outstanding invoices flag AP]={'y'}>}[Remaining Amount AP])>0,SUM({<[Outstanding invoices flag AP]={'y'}>}[Remaining Amount AP]))

Unfortunately, the KPI object only shows a minus sign as outcome.

When I'm using a table with the vendor name and the outstanding invoices flag as dimensions, the measure expression written below works.

if(sum([Remaining Amount AP]) > 0, sum([Remaining Amount AP]) )

How should I convert the total amount from the table into a KPI object?

Your help is much appreciated!

Mike

EDIT: I've attached an example Excel file with vendors, status and remaining amount. Hopefully this helps.

[kaushiknsolanki]

Try this.

SUM({<[Outstanding invoices flag AP]={'y'}>}if([Remaining Amount AP]>0,[Remaining Amount AP],0))

Regards,

Kaushik Solanki

[YoussefBelloum]

Hi,

Try to export a portion of your data in a excel file.

it would be easier to help you

[agigliotti · Accepted solution]

maybe this:

=sum(aggr( if( SUM( {<[Outstanding invoices flag AP]={'y'}>}[Remaining Amount AP] ) > 0,  SUM( {<[Outstanding invoices flag AP]={'y'}>}[Remaining Amount AP] ), 0 )

  ,[vendor name] ) )

[woshua5550]

I don't think IF statement is necessary

you can simply achieve it by

SUM({<[Outstanding invoices flag AP]={'y'},[Remaining Amount AP]={'>0'}>}[Remaining Amount AP])

[Anonymous]

Thanks for your reply Kaushik!

When using this expression, I get 0 as outcome..

Any idea why?

Regards,

Mike

[Anonymous]

Thanks for your reply Andrea! I thought of an aggr function as well, but I'm not pretty confident with those yet..

The function you've proposed shows unfortunately 0 as outcome, where this should be a bigger amount.

[Anonymous]

I've tried the similar expression before posting it, but that didn't work out.

To be sure, I've tried the expression you proposed and this also shows 0 as outcome..

Thanks for your help though!

[Anonymous]

Hi Youssef,

you're right! I thought that our experts perhaps could help me out without an example file, because they've seen this many times before. Nevertheless, every question has it's own remarks and therefore a sample file is more then helpful.

I've attached a file to the original post!

Cheers,

Mike

[agigliotti]

then check if your value in set analysis is wrote correctly:

[Outstanding invoices flag AP]={'y'}

maybe it should be in uppercase ex. [Outstanding invoices flag AP]={'Y'}