If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
I've been looking for similar issue, but i'm getting confused with the keyword
so, I have this table
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:
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))
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).
please share your expected output
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:
(I updated my original question with more explanation of my expected result).
Try this for Quantity
Try this for time
For max quantity use below
For max Time use below
Firstsortedvalue( distinct time, -aggr(sum(quantity),time))
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.
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
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.
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?