Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am new to Qlik sense and i wish to find out something please. I would say i am more a tech person. So i am able to automate many of these tasks via scripts. But i have to find an easy way to achieve a simple task to bring on board more users who have apprehension of embarking on this journey of self service analytic.
Hence, presume I have a dataset with just these 3 data fields
ID , Date of visit, Status
== =========, ======
1, 1 Jan 2023 , Completed
2, 1 Jan 2023 , Active
3, 3 Feb 2023, Completed
4, 5 Feb 2023, Completed
5, 7 Mar 2023, Completed
6, 5 Mar 2023, Active
My total completion quota is 10.
I wish to plot a stack bar chart of "cumulative total completed" over "Balance to completion" by YYYYMM of visit date
My current method
1) i have a script that converts Date of visit to YYYYMM.
2) Then i count no of completes by YYYYMM and also calculate the cumulative total
3) Then i take 10 - (cumulative total) = Balance of completion
my aim is to produce a chart like this from the 3 raw data fields. May i know can that be done easily by users using Qliksense ?
Thank you
Hey happy to help get you started. I have loaded the initial data using an inline load :
Data:
LOAD * Inline [
ID , Visit_Date, Status
1, 01/01/2023 , Completed
2, 01/01/2023 , Active
3, 03/02/2023, Completed
4, 05/02/2023, Completed
5, 07/03/2023, Completed
6, 05/03/2023, Active
];
You will then need to add a master calendar, this will create the month/year etc etc.
Then I added a variable input box and a variable to update. This allows you to create a target or the completion figure :
This means you can change the target as required in the future easily.
For the chart there are two expressions, one counts the number of completed, and the other compares to the completion target :
Completed Figure =count({<Status = {'Completed'}>}ID)
Target to Complete =$(vCompletition) - counT({<Status = {'Completed'}>}ID)
Then you chart them in a stacked bar chart :
Hopefully this helps get you started!!
thanks @dmac1971 . do you know is there a feature / function where it can calculate cumulative total ?
as in Mth 1 = T1, Mth 2 = T2 + T2, Mth 3 = T3+T2+t1 so on. depending on number of months displayed.