Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

## How to create Filter Pane and KPI by Measurement

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.

2 Solutions

Accepted Solutions
Specialist

hi,

find the script

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

hi:
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

Specialist

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

14 Replies
Creator

Hi,
Can you please attach sample QVD?

Thanks

From Nothing - To Something - To Everything
Contributor III
Author

Hi,

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

Contributor III
Author

Hi Usama,

Please find the attached qvd file

Contributor III
Author

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

Creator

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
Contributor III
Author

Hi Usama,

Could you please give me a sample script for this .

Creator

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

From Nothing - To Something - To Everything
Contributor III
Author

Hi Usama,