topic Re: Question about SUMIF in QLIKView in QlikView

Question about SUMIF in QLIKView

lucastro_123 — Sat, 16 Nov 2024 18:36:21 GMT

Hello guys!!

I'm new at QLIK View and i'm having difficulty to sum a quantity with conditions. Here's the thing:

I need to Sum all the collumn "Day to Day * Real" and divide that for the sum of the Real collumn. All that aggregate by the "Ano" collumns, the "Mês" column and the B and C collumn there in blank. All of that to appear in another collumn next to it. That would be (done in Excel):

I tried the

Sum(Aggr([Day to Day * Real],Ano,Mês,B,C))

With no luck... It's just returns 0s...

Can anyone help me?

Thanks a lot!!!

Re: Question about SUMIF in QLIKView

agigliotti — Fri, 22 May 2020 12:44:03 GMT

maybe this:
Sum( TOTAL [Day to Day * Real] ) / Sum( TOTAL Real )

Re: Question about SUMIF in QLIKView

lucastro_123 — Fri, 22 May 2020 12:46:51 GMT

It didn't work... It returns null() values.

Re: Question about SUMIF in QLIKView

agigliotti — Fri, 22 May 2020 12:54:00 GMT

please first check if the fields names in the expression are correct.

Re: Question about SUMIF in QLIKView

lucastro_123 — Fri, 22 May 2020 13:00:35 GMT

yes, they are.

Re: Question about SUMIF in QLIKView

agigliotti — Fri, 22 May 2020 13:05:11 GMT

which are the actual table's dimensions and measures ?

Re: Question about SUMIF in QLIKView

lucastro_123 — Fri, 22 May 2020 13:27:22 GMT

Column "Ano" = Year (2019,2020)

Column "Mês" = Month (1,2,3,4,5,6,7,8,9,10,12)

Column B = Information about the country (Ie: Blue, Red, Green)

Column C = The client (Ie: Russia)

Therefore the sum should be aggregate by the year (in this case 2020), by the month (4) and the Column C (Russia)

In other words i would be summing all the data from "Day to Day * Real" for the year of 2020, the month of 4 and the country of Russia, divided by the summing of all data from "Real" for the year of 2020, the month of 4 and the country of Russia.

The Programed, Real,Day to Day and "Day to Day * Real"are expressions

Re: Question about SUMIF in QLIKView

agigliotti — Fri, 22 May 2020 13:47:55 GMT

try with this expression:
Sum( TOTAL <Year,Month,Country> [Day to Day * Real] ) / Sum( TOTAL <Year,Month,Country> Real )

Re: Question about SUMIF in QLIKView

lucastro_123 — Fri, 22 May 2020 14:10:01 GMT

Still not working... If i just put Sum( TOTAL <Year,Month,Country> [Day to Day * Real] )

which should bring me the result of the sum of the "Day to Day * Real" column aggregate by the year, month and country, it brings me "0" values.

Re: Question about SUMIF in QLIKView

sunny_talwar — Fri, 22 May 2020 14:21:05 GMT

Shouldn't it be

Sum(TOTAL <Year, Month, Country> [Day to Day] * [Real])

Re: Question about SUMIF in QLIKView

lucastro_123 — Fri, 22 May 2020 14:25:20 GMT

it gets back "0" too...

Re: Question about SUMIF in QLIKView

sunny_talwar — Fri, 22 May 2020 14:38:25 GMT

Are these the fields in your dashboard?

Year
Month
Country
[Day to Day]
[Real]

Also, Year Month and Country are your dimensions in the chart?

Re: Question about SUMIF in QLIKView

lucastro_123 — Fri, 22 May 2020 14:47:02 GMT

Year = Dimension at the script
Month = Dimension at the script

Country = Dimension at the script
[Day to Day] = Expression in the chart
[Real] = Expression in the chart

The dimensions in the chart are: Year, Month, Column B(Information about the country, also from the script) and Country

The expressions are Programed, Real, Day to Day and "Day to Day * Real"

Re: Question about SUMIF in QLIKView

sunny_talwar — Fri, 22 May 2020 15:02:28 GMT

What are the expressions behind [Day to Day] and [Real]? Is it something like Sum([Day to Day]) or something?

Re: Question about SUMIF in QLIKView

lucastro_123 — Tue, 26 May 2020 20:53:18 GMT

Sorry for the late reply my friend.

Yes, it was a calculated dimension, but i was able to solve my problem using aggregate.

I used Aggr on all the sum( "day to day" x "real")

and then divided that aggr for the aggr sum("real")

Using year, month and country for the dimensions in the aggr.

Thanks for all the replies and will have another problem in my next post that is a challenge one!