topic Re: Custom Classification Filter in App Development

Custom Classification Filter

TimmyCNHi — Sat, 16 Nov 2024 18:28:59 GMT

Good day everyone 🙂

I have some kind of 'Savings overview' dashboard that allows people to filter all sorts of criteria. One of the Columns in the dashboard table has the following calculation:

=sum(if(CostOrSaving='S',
[Amount]))
-sum(if(CostOrSaving='C',
[Amount]))

In the Data manager I have created a Calculated field called Classes that does the following calculation:

If(Amount>=1000,'A',
If(Amount>=800,'B',
If(Amount>=500,'C',
If(Amount<500,'D'))))

This is pretty much how my source data looks like:

Project	Date	CostOrSaving	Amount	Classes
Donkey	14/01/2020	C	1500	A
Donkey	29/01/2020	S	700	C
Donkey	07/02/2020	S	800	B
Donkey	18/02/2020	S	1000	A
Giraffe	25/02/2020	C	800	B
Giraffe	14/03/2020	S	1700	A

When no filters are applied, the table in my dashboard shows something like:

Project	Total Saving
Donkey	1000
Giraffe	900

But when I apply 'A' in the Classes filter, it shows something like this:

Project	Total Saving
Donkey	-500
Giraffe	1700

So it's basically performing the filter first, then does the calculation in the table while it should work the other way around. This is the goal:

Project	Total Saving
Donkey	1000

How do I achieve this? I'm using Qlik Sense Desktop April 2020 Patch 1.

Cheers

Timmy

Re: Custom Classification Filter

sunny_talwar — Tue, 16 Jun 2020 13:52:56 GMT

I am not sure how you came up with the expected output? Can you share why would you see only Donkey and with 1000? Also, currently it is only showing Giraffe? It doesn't show Donkey with -500 when A is selected?

Re: Custom Classification Filter

TimmyCNHi — Wed, 17 Jun 2020 07:18:02 GMT

I'm sorry, you're right. I simplified the example a great deal compared to my real data so I expected it wouldn't show the -500 but I've now created a new app with just the example data used here and the -500 does show. The original post has been edited.

Re: Custom Classification Filter

sunny_talwar — Thu, 18 Jun 2020 12:40:56 GMT

I guess if you have selected A, why are expecting to see 1000 for Donkey? Regardless of the order of operation, if A is selected there are only tow rows for Donkey which has A. I think you might need to elaborate a little more as to how you end up with the expected output

Re: Custom Classification Filter

TimmyCNHi — Fri, 19 Jun 2020 11:46:25 GMT

Thank you for your feedback, @sunny_talwar. Good point! The logic I'm trying to achieve is that project 'Donkey' has a cost of 1500 and a combined saving of 2500, resulting in a total saving of 1000. So it should first perform the calculation (as it currently already does in the table) but then afterwards assign the 'A' classification. I understand this logic may be somewhat paradoxical and therefore impossible (?) but maybe there is a way to attack this. Perhaps I shouldn't classify the values in the data manager but rather add a measure column in the sheet's table.

Edit: adding this to the table was easy as Qlik allowed me to refer to the 'Saving' column but I can't seem to be able to refer to the 'ProjectClass' column from the filter.

Re: Custom Classification Filter

sunny_talwar — Fri, 19 Jun 2020 14:28:39 GMT

But Giraffe was associated with class A once too.. why did we remove Giraffe and only kept Donkey?

Re: Custom Classification Filter

TimmyCNHi — Mon, 22 Jun 2020 07:03:02 GMT

Good morning!

Ah but in project Giraffe there's a cost of 800 and a saving of 1700, meaning its total saving dunks below the required 1000 to attain the 'A' status. At 900 it should be appointed the B status (800-999).

Giraffe	25/02/2020	C	800	B
Giraffe	14/03/2020	S	1700	A

I believe I'm on the right track with appointing the class in the table, I just need to be able to filter that column with a filter pane.

Project	Saving	ProjectClass
Donkey	1000	A
Giraffe	900	B