Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
mslottje
Contributor II

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?

Your help is much appreciated!

Mike

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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Using if statement in KPI object

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

View solution in original post

12 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Using if statement in KPI object

Try this.

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
YoussefBelloum
Esteemed Contributor

Re: Using if statement in KPI object

Hi,

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

it would be easier to help you

Highlighted
Partner
Partner

Re: Using if statement in KPI object

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

View solution in original post

Highlighted
woshua5550
Contributor III

Re: Using if statement in KPI object

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

Highlighted
mslottje
Contributor II

Re: Using if statement in KPI object

Thanks for your reply Kaushik!

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

Any idea why?

Regards,

Mike

Highlighted
mslottje
Contributor II

Re: Using if statement in KPI object

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.

Highlighted
mslottje
Contributor II

Re: Using if statement in KPI object

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!

Highlighted
mslottje
Contributor II

Re: Using if statement in KPI object

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

Highlighted
Partner
Partner

Re: Using if statement in KPI object

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