Create a dashboard

Sourav · June 17, 2023, 3:05pm

My excel file look like this…

And i want to create a kpi dashboard with the help of this excel sheet like the given image…

Where the total source= total distinct value of the the source table
FULL RECONCILED=if the total raw count is equal to the total hrm count
NOT RECONCILED BUT JUSTIFIED=the total raw count is equal to the combined sum of hrm and reject count
And
NOT RECONCILED NOT JUSTIFIED=if the raw count not equal to the combined sum of hrm and reject count

Sanjeeb2022 · June 18, 2023, 9:29am

Hi @Sourav - Is it possible to paste raw data in comma delimited so that we can take the data and try to guide to create the dashboard.

In parallel, please have a look on the demo portal and see how different dashboards have been created.

Regards - Sanjeeb

Sourav · June 18, 2023, 9:45am

Hi @Sanjeeb sure, i will share the data here for the better understanding for making of the dashboard.

Sanjeeb2022 · June 18, 2023, 9:58am

Thanks @Sourav . This will help in building the dashboard easily.

Regards - Sanjeeb

Sourav · June 18, 2023, 11:40am

Source Table,Source Bucket,key1name,key1value,Raw count,Reject count,Hrm count,Target count,Reconciled
Address,raw,Address_id,1,1,1,0,1,No
Address,raw,Address_id,2,1,0,1,1,Yes
Address,raw,Address_id,3,1,0,1,1,Yes
Address,raw,Address_id,4,1,0,1,1,Yes
Address,raw,Address_id,5,1,0,1,1,Yes
Address,raw,Address_id,6,1,0,1,1,Yes
Address,raw,Address_id,7,1,0,1,1,Yes
Address,raw,Address_id,8,1,0,1,1,Yes
Address,raw,Address_id,9,1,0,0,0,No
Address,raw,Address_id,11,1,0,0,0,No
Address,raw,Address_id,12,1,0,0,0,No
Entity,raw,Entity_id,131,1,1,0,1,No
Entity,raw,Entity_id,546,1,1,0,1,No
Entity,raw,Entity_id,687,1,1,0,1,No
Entity,raw,Entity_id,88,1,0,1,1,Yes
Entity,raw,Entity_id,4885,1,0,1,1,Yes
Entity,raw,Entity_id,957,1,0,1,1,Yes
Entity,raw,Entity_id,231231,1,0,1,1,Yes
Entity,raw,Entity_id,312654,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,77,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,780,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,776,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,65,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,4634,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,523,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,402,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,34658,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,69,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,7,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,879,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,78,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,97,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,402,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,45,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,21,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,5,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,14,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,0,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,7657,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,79897,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,78,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,907576,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,2,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,3458,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,76,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,8786345,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,324,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,9880989,1,0,1,1,Yes
Contribution_limit,raw,Contribution_limit_id,343,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,445,1,1,0,1,No
Employee_details,raw,Employee_details_id,678,1,1,0,1,No
Employee_details,raw,Employee_details_id,564,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,857,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,687,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,678,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,890,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,687,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,90,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,327,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,765,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,34598,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,546,1,0,1,1,Yes
Employee_details,raw,Employee_details_id,567,1,0,1,1,Yes
Mifid_identidication,raw,Mifid_identidication_id,905454,1,1,0,1,No
Mifid_identidication,raw,Mifid_identidication_id,3467,1,1,0,1,No
Mifid_identidication,raw,Mifid_identidication_id,4675,1,1,0,1,No
Mifid_identidication,raw,Mifid_identidication_id,54678,1,1,0,1,No
Mifid_identidication,raw,Mifid_identidication_id,4367,1,1,0,1,No
Mifid_identidication,raw,Mifid_identidication_id,3478,1,0,1,1,Yes
Mifid_identidication,raw,Mifid_identidication_id,73,1,0,1,1,Yes
Mifid_identidication,raw,Mifid_identidication_id,7834,1,0,1,1,Yes
Mifid_identidication,raw,Mifid_identidication_id,4738,1,0,1,1,Yes
Mifid_identidication,raw,Mifid_identidication_id,3478,1,0,1,1,Yes
Mifid_identidication,raw,Mifid_identidication_id,7834,1,0,0,0,No
Asset_unitprice,raw,Asset_unitprice_id,473,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,7348,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,7834,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,7834,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,789,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,932,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,765,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,9876,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,2234,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,45,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,68,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,6543,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,9876,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,567,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,98,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,765,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,654,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,543,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,43,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,321,1,0,1,1,Yes
Asset_unitprice,raw,Asset_unitprice_id,321,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,9876,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,8765,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,87654,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,7,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,76,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,76,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,75,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,765,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,765,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,765,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,32,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,432,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,987,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,564534,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,75643,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,7564534,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,67,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,45678,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,8,1,0,1,1,Yes
Complying_fund,raw,Complying_fund_id,5678,1,0,1,1,Yes
Hi @Sanjeeb2022 shared the data here ,please have a look if this helps.
Thank and regards,
Sourav

Sourav · June 21, 2023, 9:15am

Hi @Sanjeeb2022 do you have a look at the data, i’m almost progressing but the thing is that i can’t achive those calculated kpi count as the quicksight doesn’t allow me to make a kpi with a calculated field… Need help from your side.
Regards,
SOURAV

Sanjeeb2022 · June 21, 2023, 9:19am

Hi @Sourav - Ok, I am also still thinking on the calculated field. If you can give more details, we can take help from other community members.

Tagging @eperts @Biswajit_1993 as well.

Regards - Sanjeeb

Biswajit_1993 · June 21, 2023, 3:41pm

Hi @Sourav @Sanjeeb2022

I have also started on the solution part I will provide the details calculated field after getting the results.

Thanks & Regards
Biswajit Dash

Sourav · June 21, 2023, 4:00pm

Hi @Biswajit_1993 sharing the calculated field that i created

and also as table created as per my criteria which is below…

But i can’t add the filter based on the reconciliation status in the sheet and also try to give a kpi count based on the reconciliation status but quicksight throwing me a error saying that unsupported aggregation.
Thanks and regards,
SOURAV