Custom Classification Filter

[TimmyCNHi]

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

[sunny_talwar]

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?

[TimmyCNHi]

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. 

[sunny_talwar]

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

[TimmyCNHi]

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.

[sunny_talwar]

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

[TimmyCNHi]

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