Creating a progress/aging table

[ciphergod]

Hi all,

Need some help or perhaps just some directions on how to create the table i want. I am currently tracking the response time of each team/dept to incidents/tickets in excel. I have attached how a sample table looks like.

It consist of 2 tables, one for calendar and one for working days. I usually will pull the raw data from our internal systems which comes out as excel, and copy paste into an already formulated excel file.

I want to now just place this raw file into a folder which I can just refresh within Qliksense and give me the latest data based on the raw data.

Here's what i've done so far. I have loaded two files, the one with raw data and one with the list of public holidays. I have concatenated the public holidays, and created Master Dimension for CalendarDays and WorkDays, which i've check are giving accurate output when placed in a table/chart. 

My issues starts here, how to get create an app that gives a table that looks like the one i have attached? I have totally no directions on how to start here. Is is possible to do this with scripting? If not possible, how do i go about doing it?

Would appreciate some guidance or directions.

Thanks in advance!

[Taoufiq_Zarra]

Hi,

can you share a sample data and the expected output ?

[ciphergod]

Hi Taoufiq,

I attached a sample excel file. The essential data are in the Data sheet. Holidays dates are in Holidays sheet.

The expected output of how i like the qliksense table to look like is in Table sheet.

Please advise!

Thank you in advance!

[Taoufiq_Zarra]

Hi @ciphergod 

how do you get the values?

0 DAY

1 DAY

2 DAYS

3 DAYS

4 DAYS

5 DAYS

6 DAYS

7 DAYS

8 DAYS

9 DAYS

>9 DAYS

are you calculating these values from today's date?

[ciphergod]

Hi Taoufiq,

Yes, in the excel file i attached it is based on 26th Dec which is today's date. 

Basically when the table should update daily as each day passes and new entries are added/old entries cleared, with reference from the day it is accessed.

[Taoufiq_Zarra]

Okay, this is for Calendar Day.

Dept	0 DAY
Dept 1	0
Dept 2	1
Dept 3	0
TOTAL	1

I understand that for 26/12/2019 you have a ticket_Number =30, that's why for 0 day you have 1.

but for working_date how do you get 2 for example ?

Working Day
Dept	0 DAY
Dept 1	0
Dept 2	2
Dept 3	0
TOTAL	2

[ciphergod]

Hi,

For working days because 25th Dec is Christmas, it is a holiday. Thus the one entry on 25th is considered under 0 days too, thus it is 2.

Basically Calendar days will be Today() - Date

Working days will be networkdays(Date, Today(), Holidays) 

Hope it makes sense.

[Taoufiq_Zarra]

if I can understand calendar_day, I'm not sure about working_day

if I add the 1 in 12/25/2019 why is there 1 in 12/25/2019. and why 3 in 12/24/2019?

[ciphergod]

Hi,

I'm sorry but i do not get your question.

The table basically is showing how many days has it been since the ticket is logged. Calendar days means how many calendar days has passed. Work days means how many working days(networkdays) has passed.

if you mean why some entries have 2 or 3 as values under working days, it is either because there was a holiday or weekend before that.

[Taoufiq_Zarra]

Hi,

still don't understand why in 2 Days 3 days ago. 2 days means 12/24/2019 and it's Tuesday so not preceded by a weekend.