Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Using if statement in KPI object

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?

Mike

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

1 Solution

Accepted Solutions
Partner - Champion

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

12 Replies

Try this.

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

Regards,

Kaushik Solanki

Regards,
Kaushik
Champion

Hi,

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

Partner - Champion

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

Creator III

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
Not applicable
Author

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

Any idea why?

Regards,

Mike

Anonymous
Not applicable
Author

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Partner - Champion

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'}

Tags
Community Browser