Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lemon_norang
Contributor II
Contributor II

sum distinct with unique data

I've been looking for similar issue, but i'm getting confused with the keyword

so, I have this table

typetimequantity
type_A06:0010
type_B07:004
type_C06:002
type_A07:005

 

I want to sum the quantity based on time and ignore the type. (I'm using type there so if i use "type" as filter, the quantity will be changed).

my issues are if I load the table like that, the sum result is not correct. But if I didn't load the "type" column, the quantity (in KPI) won't change based on the filter which is "type" but the sum results is correct.

the result that i want is a table like this:

timequantity
06:0012
07:009

 

I want to use the new table to display the time with highest quantity (as a KPI chart) and add comment with the quantity

the current expression I'm using is: FirstSortedValue(distinct time, aggr(sum(quantity),time))

Labels (4)
13 Replies
Qlik1_User1
Specialist
Specialist

Try this
aggr(sum(quantity),time)

Qlik1_User1_0-1595515092530.png

 

lemon_norang
Contributor II
Contributor II
Author

Hello, thank you for your answer.

I tried it, it works fine in a table, but it didn't work in KPI, the result is "-".

i'm afraid that the type is blocking this operation because when i select the distinct data for time, the type couldn't be combined (because it's different every row).

Qlik1_User1
Specialist
Specialist

please share your expected output

lemon_norang
Contributor II
Contributor II
Author

Actually, I want to display a KPI Chart about Time that has most Quantity & display the quantity as comment on that KPI chart.

so from the example data i wrote above, the expected result  is like this:

lemon_norang_0-1595582880221.png

(I updated my original question with more explanation of my expected result).

Qlik1_User1
Specialist
Specialist

Try this for Quantity

MAX(aggr(sum(quantity),time))


Try this for time
=FirstSortedValue(time, -aggr(sum(quantity),time))

 

Qlik1_User1_0-1595591139657.png

 

Kushal_Chawda

For max quantity use below

MAX(aggr(sum(quantity),time))

For max Time use below

Firstsortedvalue( distinct time, -aggr(sum(quantity),time))

lemon_norang
Contributor II
Contributor II
Author

Hi, thank you! adding the "Max" is really works.
But actually i still have problem with this..

if i use [type] as filter, the quantity didn't change based on the type.

for example:

when I choose type = type_c, the quantity should be "2" instead of "12" (the total qty of time 06:00)

is there any solution for this? i tried this: MAX(aggr(sum(quantity),time,type)) but it still didn't work

Applicable88
Creator III
Creator III

Hello,

 

MAX(aggr(sum(quantity),time,type))  won't work because you want to aggregate your data towards a time and not the type, right ? Othterwise you also won't get 12 for the first output. 

As you can see a KPI Window cannot express more than one output. So you got the wanted output after you add a Max() function, because it rightly tells you only to display the MAX VALUE which is unique. 

 

Applicable88
Creator III
Creator III

Maybe try to work with a "filterwindows" where you can choose the  right type you want to display.

 

It should display the total value of the type you choose in the filerwindow.

Like this : sum(quantity) and the time in another kpi window. 

thats imho the easiest way to solve that dynamically.

Did I miss something?