Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I've got another question.
I need to count in a text field min and max for a price.
So basically I need to show 0.22 (pink number) as min and 48.21 (red number) as max.
But when I try to count it it always show something else.
All this numbers are not in original data, they all are calculated.
I realize than I need to count it depending on fields: MonthYear, Type and Country, but don't know how.
Here is my table:
MonthYear | Type | Country | total Litres | total Amount | Price |
=sum(Litres) | = sum(Amount) | =sum(Amount)/sum(Litres) | |||
oct-2016 | type1 | FR | 1'901'848.00 | 425'740.89 | 0.22 |
dec-2016 | type1 | FR | 1'828'807.00 | 417'415.15 | 0.23 |
nov-2016 | type1 | FR | 1'687'858.00 | 374'598.90 | 0.22 |
oct-2016 | type2 | BE | 882'472.38 | 673'299.21 | 0.76 |
dec-2016 | type2 | BE | 798'774.70 | 634'574.94 | 0.79 |
nov-2016 | type2 | BE | 744'572.30 | 558'997.41 | 0.75 |
dec-2016 | type2 | ES | 636'515.96 | 488'581.45 | 0.77 |
nov-2016 | type2 | ES | 556'924.56 | 400'173.86 | 0.72 |
oct-2016 | type2 | ES | 421'531.36 | 311'164.87 | 0.74 |
oct-2016 | type2 | FR | 321'233.95 | 278'641.32 | 0.87 |
dec-2016 | type2 | FR | 249'704.06 | 226'250.38 | 0.91 |
nov-2016 | type2 | FR | 206'497.34 | 176'489.08 | 0.85 |
dec-2016 | type1 | ES | 2'322.25 | 42'316.93 | 18.22 |
dec-2016 | type1 | BE | 1'836.02 | 42'930.21 | 23.38 |
nov-2016 | type1 | ES | 1'801.00 | 37'118.23 | 20.61 |
oct-2016 | type1 | ES | 1'393.00 | 24'903.15 | 17.88 |
oct-2016 | type1 | BE | 983.00 | 47'394.90 | 48.21 |
nov-2016 | type1 | BE | 933.00 | 41'168.73 | 44.13 |
Original Data looks like:
MonthYear | Type | Country | Litres | Amount |
oct-2016 | type2 | Belgium | 0,05 | 0,04 |
oct-2016 | type2 | Belgium | 0,06 | 0,04 |
oct-2016 | type2 | Belgium | 0,08 | 0,06 |
oct-2016 | type2 | Belgium | 0,09 | 0,07 |
oct-2016 | type2 | Belgium | 0,1 | 0,08 |
oct-2016 | type2 | Belgium | 0,16 | 0,13 |
oct-2016 | type2 | Belgium | 0,29 | 0,23 |
oct-2016 | type2 | Belgium | 1,16 | 0,87 |
oct-2016 | type2 | Belgium | 1,25 | 0,97 |
oct-2016 | type2 | Belgium | 1,7 | 1,32 |
oct-2016 | type2 | Belgium | 1,71 | 1,32 |
oct-2016 | type2 | Belgium | 1,74 | 1,35 |
etc.. there are about 22453 lines in original data.
I'll be glad to answer all questions!
BR
Eugeniya
May be this
Min
=Min(Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country))
Max
=Max(Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country))
Hi Eugeniya,
May be like this
total Litres: Aggr(Sum(Num(Litres)), MonthYear, Type, Country)
total Amount: Aggr(Sum(Num(Amount)), MonthYear, Type, Country)
Price: Aggr(Sum(Num(Amount)), MonthYear, Type, Country)/Aggr(Sum(Num(Litres)), MonthYear, Type, Country)
Regards,
Andrey
So you need this in a text box object to count the min and max Amount? So you need a text box saying 2 for min and another one for max saying count of 1?
Hello!
Thanks for the attention!
Unfortunately, it's not what I wanted.
I need to count min and max price when I make a selection.
I need a text object with Min price (for a selection( MonthYear, Type, Country))
and a text object with Max price.
And because I'm not very good in explanation, I colored it pink and red.
But anyway, I've tried it, didn't work
May be this
Min
=Min(Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country))
Max
=Max(Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country))
Hello!
Yes, almost.
Min and max price.
I need one box per each.
Min should tell 0.22 (even it there are two or more).
Same for max, it should tell 48.21.
Dimension to change always MonthYear, Type and Country.
Try these
Min
=Min(Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country))
Max
=Max(Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country))
Wow, perfect!
Thanks!
How do you do that? Every time you help, it always the right answer!
How long have you been working with Qlik?
I am glad it worked... I have been working for 2 and half years now
Btw,
if I want another text box, where I want to put a name of Country with min and max price what to do?
If i put if statement:
=if(Min(Aggr(Sum(Amount)/Sum(Litres), MonthYear, Type, Country)), Country)
it wouldn't work..