topic Re: KPI in App Development

KPI

vikey — Wed, 16 Feb 2022 18:11:43 GMT

How to find out the KPI value as per the below query:

SELECT COUNT(*) FROM "XYZ" WHERE Joining Status = ('Not Joined') AND BATCH_ID = (select max(BATCHID) from "ABC") ;

Script:

LOAD

BATCH_ID,

CREATE_TIMESTAMP;

FROM "abc”;

LOAD

BATCHID,

Emp id,

Joining Status,

FROM “xyz”;

Sample data :

ABC table:

Batch_ID	Create_TimeStamp
1	20-JAN-22 03.45.44.000000000 PM
2	03-FEB-22 01.54.29.000000000 PM

xyz table:

BatchID	emp id	status
1	460	Joined
1	461	not Joined
2	462	not Joined
2	463	yet to join
2	464	not Joined
1	465	yet to join

Re: KPI

SBDataspark — Mon, 21 Feb 2022 13:19:22 GMT

Hi there,

Thank you for the sample data to test with.
Is it correct that "Batch_ID" in table ABC is namen differently than "BatchID" in table XYZ?

If that's a mistake and both have the same column name, let's say "BatchID" than this expression might fit your needs:

=Count({<status={'not Joined'},BatchID={"=$(=Max(BatchID))"}>} BatchID)

If they're not the same and you need a relationship between them you should create that in your data model by naming them the same so you can use the above expression.

Or create some kind of link table between them so you can change the expression to:

=Count({<status={'not Joined'},BatchID={"=$(=Max(Batch_ID))"}>} BatchID)

Hopefully this helps you a bit further.

Kind regards,

Sebb

Re: KPI

vikey — Mon, 21 Feb 2022 13:57:04 GMT

Thanks for your response, the above expression works for me.

as you asked for the below question.

Q: Is it correct that "Batch_ID" in table ABC is namen differently than "BatchID" in table XYZ?

A: Both have different values.

Re: KPI

SBDataspark — Mon, 21 Feb 2022 14:46:52 GMT

Hi,

Good to hear that the expression works for you!
These types of questions help me sharpen my Qlik skills as well...😉

Kind regards,

Sebb

Re: KPI

vikey — Mon, 21 Feb 2022 17:54:19 GMT

Hi,

In the script, i need to add one more table..

asd table:

BatchID	emp id	status
1	460	Exit
1	461	Exit
2	462	Migrated
2	463	Migrated
2	464	Migrated
1	465	Exit

As i need to create Bar chart , so i need count of status from xyz table and asd table where batchid= max(batchid).

Re: KPI

SBDataspark — Thu, 24 Feb 2022 13:34:45 GMT

Hi Vikey,

If I'm understanding your requirements than this can be done with the following additional load script:

// start Initial load script
[ABC]:
Load * Inline
[BatchID,Create_TimeStamp
1,20-JAN-22 03.45.44.000000000 PM
2,03-FEB-22 01.54.29.000000000 PM
];

[XYZ]:
Load * Inline
[BatchID,emp id,status,SourceTable
1,460,Joined,xyz
1,461,not Joined,xyz
2,462,not Joined,xyz
2,463,yet to join,xyz
2,464,not Joined,xyz
1,465,yet to join,xyz
];
// end initial load script

// start additional load script

// If [XYZ] and [ASD] have the same columns and are loaded right after each other you can do this
Load * Inline
[BatchID,emp id,status,SourceTable
1,460,Exit,asd
1,461,Exit,asd
2,462,Migrated,asd
2,463,Migrated,asd
2,464,Migrated,asd
1,465,Exit,asd
];

//If [XYZ] and [ASD] are loaded in different parts of your load script you can do this
// Join([XYZ])
// Load * etc.

// Both of these will result in a combined table [XYZ]

With the following measurement =Count({<BatchID={"=$(=Max(BatchID))"}>} BatchID) you can create this bar chart:

Kind regards,

Sebb

Re: KPI

vikey — Thu, 24 Feb 2022 14:19:43 GMT

Thanks for the suggestion, i have one more Query, can you please help me on it.

As i need to create pie chart , so i need status of count from xyz table and asd table where batchid= user selection batch_id from ABC table.

Re: KPI

SBDataspark — Thu, 24 Feb 2022 14:23:16 GMT

Hi Vikey,

At the moment our Qlik Sense Saas environment on the EU cloud has some problems editing apps...😉

Can you copy the bar chart, than drag and drop a pie chart onto the copy of your bar chart and choose "convert to pie chart"?

Kind regards,

Sebb

Re: KPI

vikey — Thu, 24 Feb 2022 14:28:12 GMT

Thanks for your prompt respond, what i do i mention the expression for where batchid= user selection batch_id

Re: KPI

SBDataspark — Thu, 24 Feb 2022 16:06:27 GMT

Hi Vikey,

I'm still struggling with the fact that BatchID and Batch_ID are related but named differently.
I guess it makes it easier if you name these two columns the same in your data model or create some sort of linked table to relate these two.

Is this what you're looking for?

Kind regards,

Sebb

Re: KPI

vikey — Thu, 24 Feb 2022 17:08:50 GMT

HI Sebb,

in one table it is BATCH_ID and in other it is BATCHID, but they are same, where BATCHiD Is like a foreign key.

and yes i am looking for the same as you shared in pie chart, if possible can i have the expression for Pie chart please. and can i get the condition for BATCHID= 'USER SELECTED FROM BATCHID from ABC table.

Re: KPI

SBDataspark — Mon, 28 Feb 2022 08:32:35 GMT

Hi Vikey,

Maybe the attached QVD can help you.

Kind regards,

Sebb

Re: KPI

vikey — Fri, 04 Mar 2022 14:02:18 GMT

Thank you, one more help, i want to create filter chart based on Create_TimeStamp, in that by default it should show the max(Create_TimeStamp) with other Create_TimeStamp values as a dropvalue, based on the selection timestamp dashboard values has to change.

I had attached the QVF for your reference.