Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
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
Kushal_Chawda

@lemon_norang  Ideally that expression should work for any selection as we are not restricting it for any selection. Would you be able to share sample to look at?

Qlik1_User1
Specialist
Specialist

@lemon_norang ,

For me its working, below are the expressions i am using

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

Qlik1_User1_0-1595929612448.png

 

lemon_norang
Contributor II
Contributor II
Author

@Kushal_Chawda @Qlik1_User1 @Applicable88 

thank you all for helping.

so before this, i made the same type of KPI before and it works just fine by displaying the time and its quantity, it also dynamically changes based on the type i choose (as filter)

the only difference with this KPI i'm working now is that i'm using the data by making new cross table form the main table.

the example of original table is looks like this:

TYPEtime_06time_07time_08time_09
type_a51041
type_b9212
type_c11178
type_d22812

 

because it'll be hard to make a KPI with that data, so i made new table (tbh i couldn't make this table with crosstable because of the type, so i make the table from database) so the table became like this:

TYPETIMEQTY
type_atime_065
type_atime_0710
type_atime_084
type_atime_091
type_btime_069
type_btime_072
type_btime_081
type_btime_092
type_ctime_0611
type_ctime_071
type_ctime_087
type_ctime_098
type_dtime_062
type_dtime_072
type_dtime_088
type_dtime_0912

 

now i'm confused why the KPI (both the type & qty) not changing dynamically based on the filter (which is type). is it the data i'm using that's not correct?

if i didn't add "type" to the new table, i can make it by using crosstable script, but it won't be connected with other data so it won't change if i choose some filters. then, i add the "type" to the table for it to connect with other data (for the filtering purpose). but i couldn't do that with crosstable so i made the new table directly from database and load it to qlik.

but now, even with adding "type" to the new table, the KPI i'm making still won't change dynamically..

I'm sorry if it's confusing..

Kushal_Chawda

I am still unclear what you are trying to do. Sorry can you please elaborate?