topic Re: How to achieve a basic reporting from raw data in Qlik Automate

How to achieve a basic reporting from raw data

BYCHUAIMH — Sat, 06 Jul 2024 13:52:44 GMT

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

Re: How to achieve a basic reporting from raw data

dmac1971 — Mon, 08 Jul 2024 11:47:37 GMT

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!!

Re: How to achieve a basic reporting from raw data

BYCHUAIMH — Thu, 11 Jul 2024 03:55:25 GMT

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.