Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
@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?
For me its working, below are the expressions i am using
MAX(aggr(sum(quantity),time))
FirstSortedValue(time, -aggr(sum(quantity),time))
@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..
I am still unclear what you are trying to do. Sorry can you please elaborate?