Filter by dollar amount

[BrianDH]

I am trying to filter on a dollar amount.

This does return records: =Aggr(if((SUM(BUDGET_AMT) > 1), MasterKey),MasterKey)

This does NOT return any records. =Aggr(if((SUM(BUDGET_AMT) > 1,000), MasterKey),MasterKey)

I have hundreds of records that are over 1,000. 

Looking for suggestions 

[teiswamsler]

Hi Brian

Try this
Aggr(if((SUM(BUDGET_AMT) > 1000), MasterKey),MasterKey)

Br.
Teis

[BrianDH]

OK, now I get records. However, I need to only show



Aggr(if((SUM(BUDGET_AMT) > 3,000,000), MasterKey),MasterKey)



I have 12 records that should be seen but it shows no records.

[teiswamsler]

Hi Brian

Dont use "," within the If() function. Edit value to have no seperators.

Br.
Teis

[BrianDH]

I tried this

=Aggr(if((num(SUM(BUDGET_AMT), '##########') > num(3000000, '##########')), MasterKey),MasterKey)

[teiswamsler]

Hi Brian

You keep changing your question, what do you wish the show?

Br.
Teis

[BrianDH]

Sorry

 I only want to show records that have a budget over 3 Million 

=Aggr(if((SUM(BUDGET_AMT) > num(3000000, '##########')), MasterKey),MasterKey, PROJECT_ID)

I am trying to understand how to do this in Qlik Sense.  

[teiswamsler]

Hi Brian

No problem.

try this
Aggr(if((SUM(BUDGET_AMT) > 3000000), MasterKey),MasterKey)

I your example when you use a comma "," in the value. Qlik will read it as seperate parts of the if() function.

Br.
Teis

[BrianDH]

Getting closer, see screenshot attached.   

My "Key"  is putting the rows that are over 3 Mil at the top but i am still getting other rows.

🙂

[teiswamsler]

Hi Brian

Lets try something els.

Sum( {$< ProjectKey={"=Sum(BUDGET_AMT)>3000000"}>} BUDGET_AMT )

If there is more then one measures in the table/object the set above should be use in all measures.
and in the table properties, you should exclude zero value.

Br.
Teis