topic Re: Group a field by amount in QlikView

Group a field by amount

tommasogaiaspa — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Group a field by amount

vishsaggi — Mon, 13 Aug 2018 16:39:57 GMT

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;

Re: Group a field by amount

tommasogaiaspa — Tue, 14 Aug 2018 08:03:12 GMT

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

Re: Group a field by amount

vishsaggi — Tue, 14 Aug 2018 16:03:28 GMT

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

Re: Group a field by amount

tommasogaiaspa — Thu, 16 Aug 2018 08:13:29 GMT

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

Re: Group a field by amount

vishsaggi — Thu, 16 Aug 2018 15:04:04 GMT

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)

Re: Group a field by amount

vishsaggi — Thu, 16 Aug 2018 15:28:32 GMT

Or Just this in your expression?

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