7 Replies Latest reply: Nov 15, 2016 9:25 AM by Erick Dameron

# Calculate compliance based on dates

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

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]:

rowNo() as servceId,

*,

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

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

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

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

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

clientvisit_id,

*,

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

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

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,

*,

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

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

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]:

[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]:

[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]:

[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]:

[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];

name

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

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

"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]);

"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);

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)]);

clientvisit_id,

*,

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

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] ;

• ###### Re: Calculate compliance based on dates

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.