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

    Calculate compliance based on dates

    Erick Dameron

      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
          oskars caikovskis

          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
              Erick Dameron

              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
                  oskars caikovskis

                  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
                      Erick Dameron

                      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
                          oskars caikovskis

                          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
                              Erick Dameron

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

                    • Re: Calculate compliance based on dates
                      oskars caikovskis

                      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.