topic Re: How to create Filter Pane and KPI by Measurement in App Development

How to create Filter Pane and KPI by Measurement

apichana — Wed, 20 Jan 2021 04:58:52 GMT

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.

Re: How to create Filter Pane and KPI by Measurement

Usama — Wed, 20 Jan 2021 06:44:39 GMT

Hi,
Can you please attach sample QVD?

Thanks

Re: How to create Filter Pane and KPI by Measurement

apichana — Wed, 20 Jan 2021 07:29:10 GMT

Hi,

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

Re: How to create Filter Pane and KPI by Measurement

apichana — Wed, 20 Jan 2021 07:35:57 GMT

Hi Usama,

Please find the attached qvd file

Re: How to create Filter Pane and KPI by Measurement

apichana — Wed, 20 Jan 2021 07:44:43 GMT

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

Re: How to create Filter Pane and KPI by Measurement

Usama — Wed, 20 Jan 2021 07:48:33 GMT

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

Re: How to create Filter Pane and KPI by Measurement

apichana — Wed, 20 Jan 2021 07:57:36 GMT

Hi Usama,

Could you please give me a sample script for this .

Re: How to create Filter Pane and KPI by Measurement

Usama — Wed, 20 Jan 2021 08:50:18 GMT

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

Re: How to create Filter Pane and KPI by Measurement

apichana — Wed, 20 Jan 2021 08:53:53 GMT

Hi Usama,

Thank you for your reply. Please find attached below.

Re: How to create Filter Pane and KPI by Measurement

Usama — Wed, 20 Jan 2021 08:58:54 GMT

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

Re: How to create Filter Pane and KPI by Measurement

apichana — Wed, 20 Jan 2021 09:08:44 GMT

I have calculation below as a master measurement which show % of E20 and E85 Consumption compared with total QTY_LITRE . How to convert this measurement to a dimension?

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

Re: How to create Filter Pane and KPI by Measurement

Ksrinivasan — Wed, 20 Jan 2021 18:23:43 GMT

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

Re: How to create Filter Pane and KPI by Measurement

apichana — Thu, 21 Jan 2021 02:21:10 GMT

Hi Ksrinivasan,

Thank you so much for your advice. In order to get classification of definitions, I have tried creating master dimension as expression below but it doesn't show out and I got invalid dimension. Please advise.

= If(sum({<[PRODUCT_NAME]={'GASOHOL E20', 'GASOHOL E85'}>} [QTY_LITRE]) / sum(QTY_LITRE) = 1, 'Excellent','ACTION NEEDED')

Re: How to create Filter Pane and KPI by Measurement

Ksrinivasan — Thu, 21 Jan 2021 03:42:25 GMT

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

Re: How to create Filter Pane and KPI by Measurement

apichana — Thu, 21 Jan 2021 04:24:11 GMT

Hi,

It works!! Thank you so much. Really appreciate for your help.

However, with this expression it can't be used as a dimension right? So is there any way to do the dimension in order to filter by classification like excellent, good,...