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!
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;
