Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Filter by dollar amount

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 

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Question

You say to "use this in all measures"



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



Do I replace BUDGET_AMT with the measure name/s



BTW(Sum( {$< ProjectKey={"=Sum(BUDGET_AMT)>3000000"}>} BUDGET_AMT ) this did filter my rows great until I added a second measure.


View solution in original post

13 Replies
Highlighted
Partner
Partner

Hi Brian

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

Br.
Teis

Highlighted
Creator II
Creator II

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.


Highlighted
Partner
Partner

Hi Brian

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

Br.
Teis

Highlighted
Creator II
Creator II

I tried this

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

Highlighted
Partner
Partner

Hi Brian

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

Br.
Teis

Highlighted
Creator II
Creator II

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.  

 

 

Highlighted
Partner
Partner

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

Highlighted
Creator II
Creator II

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.

🙂

Highlighted
Partner
Partner

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