topic Re: Calculate compliance based on dates in App Development

Calculate compliance based on dates

erickd1190 — Fri, 04 Nov 2016 23:34:34 GMT

Hi all,

I am new to Qlik, loving it thus far. I need to calculate my teams compliance over a time period.

Historically I have done this within excel using the following formula:

Note Day = If(Transfer date - Service Date<4,1,0)

This returns 1 for compliant and 0 for not allowing my to average the records and return a %

For example:

Sally

service id service date transfer date Note Days

1 10/1/2016 10/2/2016 1

2 10/3/2016 10/3/2016 1

3 10/4/2016 10/8/2016 0

4 10/4/2016 10/7/2016 1

I know that Sally is at 74% compliance because of the note days.

How would I achieve this within Qliksense and in turn set a Gauge visualization?

Appreciate the thoughts.

Re: Calculate compliance based on dates

Mon, 07 Nov 2016 09:04:40 GMT

If(transferDate - serviceDate<4,1,0) as noteDay;

Meaning - Let's say you have a txt file like this:

service transfer

10/1/2016 10/2/2016

10/3/2016 10/3/2016

10/4/2016 10/8/2016

10/4/2016 10/7/2016

I would load it like this:

[test]:

Load

rowNo() as servceId,

If(transferDate - serviceDate<4,1,0) as noteDay;

LOAD

Date(Date#([service])) as serviceDate,

Date(Date#([transfer])) as transferDate

FROM [lib://AttachedFiles/test.txt]

(txt, codepage is 1252, embedded labels, delimiter is spaces, msq);

Re: Calculate compliance based on dates

Mon, 07 Nov 2016 09:25:48 GMT

Oh, and about the gauge visualization - I put Avg(noteDay) as the measure, number formatting as custom w/ format pattern 0 % (or you can do 0.00% if you want) and in appearance I put range limits as 0 and 1 in the sheet. This returns 75% because 3/4 of numbers are ones. Not sure if it's what you meant. You said - 74%. I'm not an mvp.

Re: Calculate compliance based on dates

erickd1190 — Mon, 07 Nov 2016 22:16:57 GMT

Hi!

I am using web connectors for the data pull. Can I load the data this way still?

My initial try came back with an error.

Thanks,

Erick

Re: Calculate compliance based on dates

Tue, 08 Nov 2016 13:09:18 GMT

I don't see why that would cause an issue. I made a quick web connector and sort of tested it, no error messages for me. I think you just had a syntax error or an unrelated error.

Re: Calculate compliance based on dates

erickd1190 — Mon, 14 Nov 2016 16:53:22 GMT

Unfortunately it isn't working. Not sure if it is the query or what but whenever it gets to the piece below it comes back with an error.

Query is...

LOAD

clientvisit_id,

if(clientvisit.transfer_date-clientvisit.timein<4,1,0) as NoteDay;

LOAD

Date(Date#([clientvisit.timein])) as serviceDate,

Date(Date#([clientvisit.transfer_date])) as transferDate

FROM

(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

Re: Calculate compliance based on dates

Tue, 15 Nov 2016 08:10:52 GMT

Loading a single table goes from down -> up. You can't use data in a second load that isn't in the first one.

Meaning - Qlik doesn't understand what is clientvisit.transfer_date and clientvisit.timein because there is no such thing loaded in the first load. You must load using the same name as before! If(transferDate - serviceDate<4,1,0) as noteDay; There's no longer such a thing as clientvisit.transfer_date and clientvisit.timein because you haven't loaded it. And you don't have to.

While we're at it - I'm pretty sure it doesn't understand what clientvisit_id is either for this exact reason - you haven't loaded it! If it's a number in the excel sheet, then load it first! Meaning - on the lowest load. And all the following ones until you don't need it. (the star does that also) If it's a unique number that doesn't exist in the excel sheet, you must declare it - rowNo() as clientvisit_id,

LOAD

if(transferDate - serviceDate<4,1,0) as NoteDay;

LOAD

clientvisit_id,

Date(Date#([clientvisit.timein])) as serviceDate,

Date(Date#([clientvisit.transfer_date])) as transferDate

FROM

(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

TD;DL: It's like you gave qlik a sheet of paper and took it away from it before allowing it to read it.

Re: Calculate compliance based on dates

erickd1190 — Tue, 15 Nov 2016 14:25:59 GMT

Thanks for the feedback! I should of clarified but it is loaded, i was only sharing the portion of my query that wasn't working.

This is th full query

[Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element_9f18d851-1206-ffef-77df-c152259a]:

LOAD [name],

[type],

[minOccurs],

[%Key_Envelope_04722DC39F37DE52]

FROM [lib://Client Visits (tbh_erickd)]

(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element]);

[Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table_069f33a2-0e7c-217a-f34d-d0d7f2fb]:

LOAD [diffgr:id],

[msdata:rowOrder],

[client_id],

[cptcode],

[timein],

[emp_id],

[rate],

[units_of_svc],

[appr],

[clientvisit_id],

[program_id],

[visittype_id],

[location_id],

[billing_group_id],

[transfer_date],

[non_billable],

[pri_payer_id],

[payer_id],

[status],

[visittype],

[team_id],

[duration],

[service_amount],

[emp_name],

[timeout],

[visit_dateday],

[cpt_modifier1],

[%Key_Envelope_04722DC39F37DE52]

FROM [lib://Client Visits (tbh_erickd)]

(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

[Envelope_a8d6a2f1-79d7-df9d-d849-44eeda3b]:

LOAD [xmlns:soap],

[xmlns:xsi],

[xmlns:xsd],

[Body/ExportDataSetResponse/xmlns],

[Body/ExportDataSetResponse/ExportDataSetResult/diffgram/xmlns:msdata],

[Body/ExportDataSetResponse/ExportDataSetResult/diffgram/xmlns:diffgr],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/id],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/xmlns:xs],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/xmlns:msdata],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/name],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/msdata:IsDataSet],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/msdata:UseCurrentLocale],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/minOccurs],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/maxOccurs],

[Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/name],

[%Key_Envelope_04722DC39F37DE52]

FROM [lib://Client Visits (tbh_erickd)]

(XmlSimple, table is Envelope);

[Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table_14e6bca2-f212-e072-c299-bc9dd3bf]:

LOAD [program_id],

[program_code]

FROM [lib://Programs (tbh_erickd)]

(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

RENAME TABLE [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element_9f18d851-1206-ffef-77df-c152259a] TO [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element];

RENAME TABLE [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table_069f33a2-0e7c-217a-f34d-d0d7f2fb] TO [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table];

RENAME TABLE [Envelope_a8d6a2f1-79d7-df9d-d849-44eeda3b] TO [Envelope];

RENAME TABLE [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table_14e6bca2-f212-e072-c299-bc9dd3bf] TO [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table-1];

LOAD

name

FROM [lib://C-employee (tbh_erickd)]

(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/complexType/sequence/element]);

LOAD

"diffgr:id",

"msdata:rowOrder",

emp_id,

external_id,

%Key_Envelope_04722DC39F37DE52

FROM [lib://C-employee (tbh_erickd)]

(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

LOAD

"xmlns:soap",

"xmlns:xsi",

"xmlns:xsd",

"Body/ExportDataSetResponse/xmlns",

"Body/ExportDataSetResponse/ExportDataSetResult/diffgram/xmlns:msdata",

"Body/ExportDataSetResponse/ExportDataSetResult/diffgram/xmlns:diffgr",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/id",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/xmlns:xs",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/xmlns:msdata",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/name",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/msdata:IsDataSet",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/msdata:UseCurrentLocale",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/minOccurs",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/maxOccurs",

"Body/ExportDataSetResponse/ExportDataSetResult/schema/element/complexType/choice/element/name",

%Key_Envelope_04722DC39F37DE52

FROM [lib://C-employee (tbh_erickd)]

(XmlSimple, table is Envelope);

LOAD

Empno,

Employee,

"Code Description",

JuneHours,

JulyHours,

AugHours,

SepHours,

OctHours,

NovHours,

F10,

F11,

"Total Hours"

FROM [lib://AttachedFiles/Employee Hours.xlsx]

(ooxml, embedded labels, table is [Analysis (2)]);

LOAD

clientvisit_id,

if(clientvisit.transfer_date-clientvisit.timein<4,1,0) as NoteDay;

LOAD

Date(Date#([clientvisit.timein])) as serviceDate,

Date(Date#([clientvisit.transfer_date])) as transferDate

FROM

(XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

[autoCalendar]:

DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

Month($1) AS [Month] Tagged ('$month', '$cyclic'),

Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');

DERIVE FIELDS FROM FIELDS [timein], [transfer_date], [timeout] USING [autoCalendar] ;