Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
pauluk__
Contributor III
Contributor III

Inconsistency with aggregate expression results?

Hi all,

I have an aggregate expression my load script, which follows the basic logic below:

LOAD

Date

FieldA,

Sum(MetricA) AS MetricA,

Sum(MetricB) AS MetricB,

Sum(MetricA)/Sum(MetricB) AS MetricC

Resident CalculationTable

GROUP BY Date, FieldA

When I put this into a table, I find that MetricC does not equal MetricA/MetricB for about half of the rows. In fact, it is out by a factor of 10. I have been trying to figure this out and I am absolutely stumped. Are we able to use expressions with multiple aggregates as above or should I work around this?

Thanks.

1 Solution

Accepted Solutions
Or
MVP
MVP

First thing I'd check is that the Date column is actually a date (or apply DayName() to it). If the underlying values include timestamps, your grouping will be off even if the end result table only displays the date portion of the stamp.

I'm not aware of any problems with the syntax above - sum(A)/Sum(B) shouldn't be an issue. That said, keep in mind that this will not aggregate to a weighted average in your charts later on - that might be a positive or a negative thing depending on what the metric represents.

Temp:

LOAD * INLINE [

    F1, F2, F3, F4

    A, A, 10, 10

    A, A, 20, 20

    A, A, 15, 30

    A, B, 100, 100

    A, B, 50, 200

];

Load F1, F2, sum(F3) as MetricA, sum(F4) as MetricB, Sum(F3)/Sum(F4) as MetricC

Resident Temp

GROUP BY F1, F2;

Drop table Temp;

View solution in original post

3 Replies
Anil_Babu_Samineni

Again you need to calculate one more resident because Sum(Sum(Field)) not works even in SQL. You could use one more time

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Or
MVP
MVP

First thing I'd check is that the Date column is actually a date (or apply DayName() to it). If the underlying values include timestamps, your grouping will be off even if the end result table only displays the date portion of the stamp.

I'm not aware of any problems with the syntax above - sum(A)/Sum(B) shouldn't be an issue. That said, keep in mind that this will not aggregate to a weighted average in your charts later on - that might be a positive or a negative thing depending on what the metric represents.

Temp:

LOAD * INLINE [

    F1, F2, F3, F4

    A, A, 10, 10

    A, A, 20, 20

    A, A, 15, 30

    A, B, 100, 100

    A, B, 50, 200

];

Load F1, F2, sum(F3) as MetricA, sum(F4) as MetricB, Sum(F3)/Sum(F4) as MetricC

Resident Temp

GROUP BY F1, F2;

Drop table Temp;

pauluk__
Contributor III
Contributor III
Author

Thanks Or. Unfortunately this was my mistake. I was doing another level of aggregation I had forgotten about. Thanks for the response though.