Adding a filter

[Anonymous]

Hello everyone!

I have a table on my dashboard with values of orders and requests. And I want to compare these two values using backward color and a filter. My code with backward color works fine but I have problems with adding a filter pane. Now I have this:

=if(((([Order Value])-([Request Value]))/(([Request Value])))<=-0.3,'less than request on 30%',
if(((([Order Value])-([Request Value]))/(([Request Value])))<=-0.1,'less than request from 10 to 30%',
if(((([Order Value])-([Request Value]))/(([Request Value])))<=0.1,'don't differ from request more than 10%',
if(((([Order Value])-([Request Value]))/(([Request Value])))<=0.3,'more than request from 10% to 30%',
'more than request on 30%'))))

And when I use my filter to the table it shows me right values and some not the right ones. I can't understand why this is happening. Really need your help!

[Anonymous]

Maybe I did the wrong thing. Could you please help me with creating a new field?

[sunny_talwar]

Your Order and Request are in different tables... in order to get this in the script, you will have to join the tables together into a single table... try something like this and see if that works

Table:
LOAD 
	[Ключ Заказа] AS [Ключ Заказа-Ключь Заказа],
	[Заказ: Количество из заказа на поставку, БЕИ] AS [Order]
 FROM [lib://Desktop/Заказ для Qlik Sense.xlsx]
(ooxml, embedded labels, table is Sheet1);

Join (Table)
LOAD 
	[Ключ Заявки],
	[Ключь Заказа] AS [Ключ Заказа-Ключь Заказа]
 FROM [lib://Desktop/1_Сводная между заказами и заявками.xlsx]
(ooxml, embedded labels, table is Sheet1);

Join (Table)
LOAD 
	[Ключ Заявки],
	[Заявка: Количество из заявки на закупку] AS [Request]
 FROM [lib://Desktop/Заявка для Qlik Sense.xlsx]
(ooxml, embedded labels, table is Sheet1);

Sheet1:
LOAD *,
	 if((([Order]-[Request])/([Request])) <= -0.3, 'less than request 30%',
	 if((([Order]-[Request])/([Request])) <= -0.1, 'less than request 10 to 30%',
	 if((([Order]-[Request])/([Request])) <=  0.1, 'do not differ from request more than 10%',
	 if((([Order]-[Request])/([Request])) <=  0.3, 'more than request 10% to 30%', 'more than request 30%')))) as NewFieldName
Resident Table;

DROP Table Table;

[Anonymous]

too difficult for me 😞

[sunny_talwar]

What is difficult for you?