Group a field by amount

[tommasogaiaspa]

Hi I'm new in Qlik, I have a table with the city field and amount.
I would like to gather all the cities in a single field with amounts less than a certain value.
You can help me thanks

[vishsaggi]

can you share some sample data with your expected output?

May be try like:

City:

LOAD City, Amount

From Tablename;

LEFT JOIN(City)

LOAD City,

           Sum(If(Amount < yourcertainvalue, Amount)) AS NewAmount

Resident City

Group By City;

[tommasogaiaspa]

This is the table with the data.
The common field references the table of municipalities while the total amount field refers to another table.
I would like to be able to group in a single field the amounts of less than 50,000.
Thanks for your help

[vishsaggi]

Did you try in your straight table like in expr: and is your ImportoTotale a field or an expression?

= Sum(Aggr(IF(Sum(Amount) <= 50000, Amount), City))

[tommasogaiaspa]

I attach the test qvw file
I would like to group the common field with amounts less than 50,000 in a single field called "other municipalities"
Thanks for the reply

[vishsaggi]

Are you looking for this?

= If(sum({$< [PROVINCIA]={[LUCCA]} >}IMPORTO)<Num(50000, '#.##0,00'), sum({$< [PROVINCIA]={[LUCCA]}>}IMPORTO))

Or in calculated dimension

Dim:

= Aggr(IF(Sum({<PROVINCIA = {LUCCA}>}IMPORTO)<Num(50000,'#.##0,00'),COMUNE),COMUNE)

Expr:

Sum({<[PROVINCIA]={[LUCCA]} >}IMPORTO)

[vishsaggi · Accepted solution]

Or Just this in your expression?

= IF(Sum({<PROVINCIA = {LUCCA}>}IMPORTO)<Num(50000,'#.##0,00'), IMPORTO)