sum distinct with unique data

[lemon_norang]

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

so, I have this table

type	time	quantity
type_A	06:00	10
type_B	07:00	4
type_C	06:00	2
type_A	07:00	5

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:

time	quantity
06:00	12
07:00	9

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))

[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]

@lemon_norang ,

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

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

[lemon_norang]

@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:

TYPE	time_06	time_07	time_08	time_09
type_a	5	10	4	1
type_b	9	2	1	2
type_c	11	1	7	8
type_d	2	2	8	12

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:

TYPE	TIME	QTY
type_a	time_06	5
type_a	time_07	10
type_a	time_08	4
type_a	time_09	1
type_b	time_06	9
type_b	time_07	2
type_b	time_08	1
type_b	time_09	2
type_c	time_06	11
type_c	time_07	1
type_c	time_08	7
type_c	time_09	8
type_d	time_06	2
type_d	time_07	2
type_d	time_08	8
type_d	time_09	12

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?