Solved: Re: How to create Filter Pane and KPI by Measureme... - Qlik Community

apichana · ‎2021-01-19

Hi,

My raw data look like below. I created master item for Measurement as below.

(sum({<[PRODUCT_NAME]={'GASOHOL E20', 'GASOHOL E85'}>} [QTY_LITRE]) / sum(QTY_LITRE)

to represent %E20orE85 Consumption per User Name.

Date	User Name	Product_Name	QTY_LITRE	%E20orE85Consumption
01/01/2021	AAAA	E20	20
02/01/2021	AAAA	E85	30
03/01/2021	AAAA	DIESEL	15
01/01/2021	BBB	DIESEL	10
02/01/2021	BBB	DIESEL	20
03/01/2021	BBB	DIESEL	30

The question is

1. How can I have a filter pane on Dashboard to show as below criteria

Definition	E20 or E85Consumption
ACTION NEEDED	E20 or E85 = 0 %
Need Improvement	E20 or E85 < 80%
GOOD	E20 or E85 > 80%
EXCELLENT	E20 or E85 = 100%

2. How can I create each KPI to count no. of transaction as above criteria.

Please give me an advice. Thank you.

Ksrinivasan · ‎2021-01-20

hi,

find the script

Gas:
LOAD
"DATE",
UserName,
Product,
"QTY(L)"
FROM [SSSS1.xlsx]
(ooxml, embedded labels, table is gasolain)
where WildMatch(Product,'GASOHOL*');

hi:
Load
Product as TProduct,
Sum("QTY(L)") as Tot
Resident [Gas]

Group by Product;

result and expression

further you use if condition to get classification of Definitions,

ksrinivasan

View solution in original post

Ksrinivasan · ‎2021-01-20

hi,

use this expression as Measure

=if(((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))='0','Action Need',
if(((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))>'0' and
((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))<'80','Need Improvement',
if(((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))>'80' and
((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))<'99','Good',
if(((Sum({$<Product={'GASOHOL E20', 'GASOHOL E85'}>} [QTY(L)]))/Sum(Tot))='100','Excellant',''))))

result

ksrinivasan

View solution in original post

Usama · ‎2021-01-20

Hi,
Can you please attach sample QVD?

Thanks

From Nothing - To Something - To Everything

apichana · ‎2021-01-20

Hi,

How to attach the file? There is no function to attach file here.

apichana · ‎2021-01-20

Hi Usama,

Please find the attached qvd file

apichana · ‎2021-01-20

It seems like I can't attach qvd file. there was an error

Usama · ‎2021-01-20

No worries.
To add in filter pane: You have to create it as a dimension in script using if conditions and for KPI you can use this dimension in set analysis to get the count.

Thanks

From Nothing - To Something - To Everything

apichana · ‎2021-01-20

Hi Usama,

Could you please give me a sample script for this .

Usama · ‎2021-01-20

Could you please attach sample dataset I need it to test first before giving you solution

From Nothing - To Something - To Everything

apichana · ‎2021-01-20

Hi Usama,

Thank you for your reply. Please find attached below.

Usama · ‎2021-01-20

How you are calculating consumption here as you mentioned in the expected output?

From Nothing - To Something - To Everything

How to create Filter Pane and KPI by Measurement