Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
agigliotti
Honored Contributor II

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

12 Replies

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

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

agigliotti
Honored Contributor II

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

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

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

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.

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!

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

agigliotti
Honored Contributor II

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

Community Browser