Does anyone have experience implementing healthy planet along with Org Filtering for Clairty and Caboodle? How did you manage the downstream impact to Qlik or other downstream solutions without having to migrate every solution one by one? Epic tells us Org filtering populates new schemas for clarity and caboodle, and many of our queries are fully qualified including the schema name. 

We have a multi tier architecture in place, but we would still have to migrate every extract one by one...Hoping there's a better way.

Thanks!

Data visualization helps to tell patient stories.

Check out this video ( https://youtu.be/CBnaPbGGWfc ) of various Qlik implementations for Healthcare including statistical process control charts (SPC), Vizlib dashboards, Apple HealthKit, and NHS Command Centers.

#Qlik #QlikSense #HealthTech #analytics #dataviz

@vizlibmartin @danielgargiulo 

Hi, 

Am new to Qlikview . I have attended the training last week and trying to implement for the health domain.

Basically for each admission Id, we do have the admission date and Discharge date attributes. At times discharge attribute will be null( Empty) , for the Patients still in hospital.

So I want to include the records only with a valid discharge date and am trying to use the below expression to calculate the average length of stay ( i.e Dischargedate diff admission date ) but it does not work. Could you please help

Avg({$<Discharge_date={">=Date(01/01/2018)"}>}-admission_date).

Also, is there any null function check in qlikview?

backend code for tables load :

Load *,

Year(LoadDate) as admission_Year,
Month(LoadDate) as admission_Month,
Floor(LoadDate) as admission_date,
Floor(UnLoadDate) as Discharge_date;

LOAD ENCNTR_ID,

REG_DT_TM,
DISCH_DT_TM,
Date(Date#(REG_DT_TM,'DD/MM/YYYY')) as LoadDate,
Date(Date#(DISCH_DT_TM,'DD/MM/YYYY')) as UnLoadDate

In a set analysis how to excluded everthing but a table , for example :

I have a INSURED table , I want to apply an set analysis only to this
table ignoring to the others.

I want to count how many insured we have in a period for example but I won´t
to aplly to another column/dimensions.

Apply all filters setted to then table INSURED and not aplly to another.

Thank you.

Hi,

I am trying to use a color by expression but the legend does not appear, how can I use a color by expression and the legend to appear?

The code for color by expression was:

IF([Mujer con sífilis gestacional] = 1,RGB(255,124,125),
IF([Mujer con sífilis gestacional] = 2,RGB(169,208,142), RGB(255,230,153)))

Hi, I have been using qlikview for awhile now, but I am by no means an expert. I have a situation whereas when I add the encounter key I get the results I want when I remove it from the dimensions it does no longer work. Below I am trying to sum a count based on a charge qty within each encounter ID that the charges are > 0.

Variable Obscriteria holds my criteria for my type of patients = visit type, revenue line etc.. It does not exclude where charges are <=0, I could not get it to work in my script that way.

Below is my counter. Please let me know what I can do. I appreciate it.

if(sum({$<MonthYear=,
[Fiscal Year] = {$(vFYCurrent)},
[Discharge Date]={'<=$(vMaxDate)'},
$(vObsCriteria),
[Encounter ID]
>}
[Charge Quantity])
sum({$<
MonthYear=,
[Fiscal Year] = {$(vFYCurrent)},
[Discharge Date]={'<=$(vMaxDate)'},
$(vObsCriteria)
>}
EncounterCount),0)

We are in the process of getting our Qlik Sense deployment rolled out and I just ran into an issue today that I'm not sure how to solve.

We have 3 separate environments (Dev, TST and PRD).  Now I have created a script to determine what environment you are in, be it dev, tst or prd.  This allows us to use txt files that store ODBC connections and txt files for variables.  So no matter what environment you are in Qlik Sense knows where to look for those files or where to store files.  This works great in QlikView.

The issue I'm having now though is with Data Connections, specifically for ODBC connections.  In dev we want our developers to use their own credentials for the ODBC connections, but in tst and prd we use Service Accounts.  This is so our auditing group can differentiate who or what system is connecting to the databases.  We don't want to use the developers credentials in tst and prd as they aren't actually connecting, but also we have to change our passwords every 90 days.

So the issue I'm running into is that I setup a data connection in all 3 environments.  So we have data connection ODBC_QS_CLARITY.  Now this also appends my user id to the name (very annoying WTH).  So the actual name of the Data Connection is now ODBC_QS_CLARITY (jkofoot).  So in tst and prd this is setup to use our service account, no issues.  But if we want developers to use these ODBC connections but use their credentials, they have to edit the connection and when they do, their userid now gets appended to the data connection name.  So now we would have a name like ODBC_QS_CLARITY (jkofoot) (asmith).  
Also we want to store the connection string in a txt file which we reference by knowing what environment we are coming from.  This worked awesome in QlikView because then each developer could hide their connection string as it included the username and password in the connection string.  But now it is just a Lib statement that doesn't have the userid/password in it.

Basically just want to know how other groups are handling having developers use their own credentials for connecting to databases, but using service accounts for connecting to databases in tst or prd.  Basically I'm not seeing a good way of doing it, without just resorting to using service account for everyone.  Don't want to do that though as it has the potential to give more access to the developer than they should have.

https://lnkd.in/euPnKN5

Join Dalton Ruer and Joe Warbington on March 7th as they cover one of the newest innovations at Qlik: CrunchBot.

They'll explore how the AI-powered natural language Bot works within Healthcare and with common messaging platforms (like Slack, Microsoft Teams, Salesforce, Skype, etc.)

Qlik CrunchBot for Healthcare

This webinar will feature the latest addition to the Qlik Analytics Platform – CrunchBot, a conversational analytics tool that uses natural language processing and interfaces with popular collaboration tools such as Slack,  Skype, Microsoft Teams, etc.

Learn how CrunchBot is being applied throughout healthcare organizations to improve data literacy and adoption.  The webinar will also explore what chatting with your data can mean for achieving the Quadruple Aim.

#CrunchBot #NLG #NLP #AI #Chatbot

Corrected_Fact:
NOCONCATENATE
LOAD *
RESIDENT [Master Fact]
WHERE match(Corrected, 'Y');

DROP FIELDS [Posting Date] FROM Corrected_Fact;

LEFT JOIN(Corrected_Fact)
LOAD DISTINCT
[Corrected Date]
, [Corrected Date] AS [Posting Date]
RESIDENT Corrected_Fact;

DROP Fields [Corrected Date];
DROP Fields Corrected FROM [Master Fact];

// Unable to understand from here

LEFT JOIN ([Master Fact])
LOAD 'N' AS Corrected
Autogenerate 1;

CONCATENATE([Master Fact])
LOAD *
RESIDENT Corrected_Fact;

DROP TABLES Corrected_Fact;

I have this code in one of the apps. I am unable to understand the code entirely. It will be a great help if someone can explain to me what exactly the code is doing.

Thanks in advance.

Using Qlik GeoAnalytics and a few open-source tools, you can create engaging visualizations tied to your data such as this. Ever wondered how else the number X-rays and MRI scans received at hospitals can be visualised? Just a simple click and you can find out what individual body parts had the most scans!

The GeoAnalytics team wrote up a tutorial on how to do things like this: https://community.qlik.com/t5/Qlik-GeoAnalytics-Documents/Digitizing-floor-plans-for-use-in-Qlik-GeoAnalytics-QGA/ta-p/1489032

I have an app that contains 3 years of data and I want to default a filter to the most recent completed pay period. What is a good way to accomplish this on Qlik Sense April 2018?

I have implemented apps with the set analysis approach below and it leaves a lot to be desired...

if(getselectedcount(PayPeriodNum) = 0, Sum({$<PayPeriodNum={$(DefaultPayPeriod)}>} Hours), Sum(Hours))

I was considering using defaultSelectionExtension, has anyone had luck with that extension?

I am ready to learn a better way. I appreciate any assistance you can provide!

Hi,

I apologize first of all if I ask for elementary things.

The question is...i have a articulate database that contain a lot of information in different tabels.

I perfectly create the script that gives me all the information necessary to calculate what i want...but i have a lot of problems to create a dinamic table that give me instantly all the information to calculate the costs of a haemodynamic procedure.

Currently I have a table that provides in output the results as shown in the attached excel (SHEET 1-TEST_FROMAT_ACTUAL).

The result is provided by a qlik table configured as visible in the PDF attached screenshots_table_qlik

NOW I HAVE 2 OBJECTIVES:

--1--

Because each procedure have a different number of fields utilized...(es. is possible that there are 4 operator, 10 material utilized, 5 procedure executed....and in another procedure 2 operator, 5 material utilized, 3 procedure executed) ...I MUST CREATE A TABLE THAT DYNAMICALLY INSERT ONCE IN THE ID ROW ALL THE FIELDS (The desidered output is showed in the attached excel (SHEET 2-TEST_FROMAT_IDEAL).

--2--

Because the fields are entities without economic value, I have to calculate the value of each field a posteriori!!!

And so...

2.1 I must create a table or excel that assign an economic value to each field?? How I can do it and how i can insert this information inside the qlik table??

2.2. I have to be able to calculate every economic value as shown  in the attached excel (SHEET 3-TEST_FROMAT_IDEAL_FINAL)...How i can put this information inside the qlik dashbboard?

I hope that someone can help me!!!

TY so much.

If you weren't already aware I published a mocked set of data for new users to play with and learn modeling and scripting techniques with called General Hospital. Check it out here. 

But check this out ... now I've kicked it up a notch. With the help of our partner Crunchbot AI you can now check out the data from General Hospital via Conversational Analytics. That's right. You simply ask questions and Crunchbot provides you the answers. Here is the best part ... you can ask them where you work. From the hub. From inside an app. Or embedded into some other workflow or messaging service. You can watch a recent webinar I conducted to catch up quickly by clicking here. 

Here are my top 3 reasons why you might want to check this out

1. There are users who don't want to find the hub, scroll to a stream, pick the 12'th app and go to the 3rd screen to find a chart. They are standing at an elevator or sitting in a meeting room and they just want to ask a question. 

2. It is built on top of Qlik Sense. So you aren't just getting answers about the data, you are getting answers based on the governance you add to applications via Master Dimensions and Master Measures. 

3. If you are a QlikView customer still struggling to understand what Qlik Sense offers that QlikView doesn't this is the perfect chance to find out. You now have a solution to provide access to those Business Analysts who ask questions faster than your QlikView development team can create change requests.

Under the covers Crunchbot AI utilizes some really cool technology. I don't want to spend too much time writing about them so if technology is your jam just click on any of the terms and you will get more details about that technology. 

• Natural Language Understanding
• Natural Language Processing
• Natural Language Generation

I hear ya. "Quit teasing me and just let me get my hands on it." Well hear you go. Simply click this link and you can have your first conversation with data.  The bot will prompt you for your name and email address, then it will send you a token so that it knows who you are. On the left side of the screen you will find help information and some suggestions for questions you can ask. But don't let you imagine stop with the basics. If you follow the link above for the General Hospital Data Model image you will get an idea for what other data the application contains. Ask about any of it. Ask the questions you would want the answers to if it was your data.

Hi all,

it has been a while I blogged about my favorite BI took, QlikView. I worked on a very cool project back in 2016 and finally blogged about it. Hope you will enjoy.

A silent killer

Sepsis is the body’s extreme response to an infection. It is a life-threatening medical emergency. Sepsis happens when an infection you already have —in your skin, lungs, urinary tract, or somewhere else—triggers a chain reaction throughout your body. Without timely treatment, sepsis can rapidly lead to tissue damage, organ failure, and death. 1

More than 1.5 million people get sepsis each year in the U.S. At least 250,000 Americans die from sepsis each year 2. Sepsis kills more people than breast cancer, prostate cancer, and AIDS...Combined3.

Yet, not many people know about it. We've all heard so much about Ebola and H1N1, but not many of us have heard about Sepsis. Who is at risk to get Ebola or H1N1? A few...Who is at risk to get Sepsis? We are all! Sepsis does not discriminate. It kills the young or the old, the rich or the poor, and it kills quickly.

https://boristyukin.com/sepsis-dashboard/

Hi,

Could anyone help please,

I want to make table join with a key and range of dates, but i don't know why it is duplicated the register.

[La_33]:
LOAD
NIT_IPS&'-'&COD_SERVICIO&'-'&TIPO_CONTRATO AS LLAVE1,
NIT_IPS&'-'&COD_SERVICIO&'-'&TIPO_CONTRATO AS LLAVE2,
NUM_FACTURA,
NUM_RADICA,
ESTADO_RADICA,
ITEM,
TIPO_DOC,
DOC_AFILIADO,
TIPO_DOC&'-'&DOC_AFILIADO AS DOCUMENTO,
COD_SEXO,
EDAD,
DIAGNOSTICO,
DESCR_DIAGNOSTICO,
DIA_RADICACION,
MES_RADICACION,
ANIO_RADICACION,
DIA_RADICACION&'/'&MES_RADICACION&'/'&ANIO_RADICACION as Fecha_Radicacion,
DIA_CONTABLE,
MES_CONTABLE,
ANIO_CONTABLE,
DIA_CONTABLE&'/'&MES_CONTABLE&'/'&ANIO_CONTABLE as Fecha_Contable,
DIA_PRESTACION,
MES_PRESTACION,
ANIO_PRESTACION,
// ANIO_PRESTACION as PERIODO_TARIFA,
DIA_PRESTACION&'/'&MES_PRESTACION&'/'&ANIO_PRESTACION as Fecha_Prestacion,
CCONTABLE,
DESC_CONCEPTO_CONTABLE,
VALOR_COBRADOIPS,
VALOR_APAGAR,
CANTIDAD,
VALOR_APAGAR/CANTIDAD as VALOR_UNITARIO,
RAZON_SOCIAL,
NIT_IPS AS NIT_IPS_INTEGRACION,
MUNICIPIO_AFI,
if(MUNICIPIO_AFI = 'MEDELLIN','MEDELLIN','RESTO') as Dist.Mun.AFI,
DEPARTAMENTO_AFI,
REGION_AFI,
MUNICIPIO_IPS,
if(MUNICIPIO_IPS='MEDELLIN','MEDELLIN','RESTO') as Dist.Mun.IPS,
DEPARTAMENTO_IPS,
REGION_IPS,
COD_SERVICIO AS C_SERVICIO,
if(index(COD_SERVICIO, '-', 1) > 0,
left(COD_SERVICIO,
index(COD_SERVICIO, '-', 1) - 1), COD_SERVICIO) as COD_SERVICIO_SIN_GUION,
COD_GRUPO,
COD_SUBGRUPO,
DESCR_SERVICIO,
COD_PATOLOGIA,
DESC_PATOLOGIA,
TIPO_CONTRATO AS TIPO_CONTRATO_33
// , TRIM(TIPO_CONTRATO) AS TIPO_CONTRATO_ANTIGUO,
// COD_SERVICIO as COD_SERVICIO_CONTRATO_ANTIGUO,
// NIT_IPS AS NIT_IPS_CONTRATO_ANTIGUO
FROM [lib://qvdsalud (integrasavia_svcqlik)/Contabilizacion_Contable.qvd]
(qvd);

NoConcatenate

[La_33_integracions]:
Load
LLAVE1,
LLAVE2,
NUM_FACTURA,
NUM_RADICA,
ESTADO_RADICA,
ITEM,
TIPO_DOC,
DOC_AFILIADO,
DOCUMENTO,
COD_SEXO,
EDAD,
DIAGNOSTICO,
DESCR_DIAGNOSTICO,
DIA_RADICACION,
MES_RADICACION,
ANIO_RADICACION,
DIA_CONTABLE,
MES_CONTABLE,
ANIO_CONTABLE,
DIA_PRESTACION,
MES_PRESTACION,
ANIO_PRESTACION,
CCONTABLE,
DESC_CONCEPTO_CONTABLE,
VALOR_COBRADOIPS,
VALOR_APAGAR,
CANTIDAD,
VALOR_APAGAR/CANTIDAD as VALOR_UNITARIO,
RAZON_SOCIAL,
NIT_IPS_INTEGRACION,
MUNICIPIO_AFI,
DATE(Date#(Fecha_Prestacion,'DD/MM/YYYY')) AS Fecha_Prestacion,
DATE(Date#(Fecha_Radicacion,'DD/MM/YYYY')) AS Fecha_Radicacion,
DATE(Date#(Fecha_Contable,'DD/MM/YYYY')) AS Fecha_Contable,
Dist.Mun.AFI,
DEPARTAMENTO_AFI,
REGION_AFI,
MUNICIPIO_IPS,
Dist.Mun.IPS,
DEPARTAMENTO_IPS,
REGION_IPS,
C_SERVICIO,
COD_SERVICIO_SIN_GUION,
COD_GRUPO,
COD_SUBGRUPO,
DESCR_SERVICIO,
COD_PATOLOGIA,
DESC_PATOLOGIA,
TIPO_CONTRATO_33
Resident La_33
Where ANIO_PRESTACION>2017 and (NIT_IPS_INTEGRACION='900625317' or NIT_IPS_INTEGRACION='890904646' or NIT_IPS_INTEGRACION='800058016');
Drop Table La_33;

CONTRATOS:
LOAD
NIT_IPS&'-'&COD_SERVICIO&'-'&IF(MODELO_CONTRATA='EVENTO','E','OTROS') AS LLAVE1,
COD_SERVICIO,
COD_SUBGRUPO as SUBGRUPO,
COD_GRUPO AS GRUPO,
DESCR_SERVICIO AS DESC_SERVICIO,
ESTADO_CONTRATO,
CONTRATO_ENTIDAD,
CONTRATO_LEGAL,
NIVEL_COMPLEJIDAD,
MODELO_CONTRATA,
IF(trim(MODELO_CONTRATA)='EVENTO','E','OTROS') AS TIPO_CONTRATO,
DATE(Date#(FECHAINICIO,'DD/MM/YYYY')) AS FECHAINICIO,
DATE(Date#(FECHAFINAL,'DD/MM/YYYY')) AS FECHAFINAL,
AddMonths(DATE(Date#(FECHAINICIO,'DD/MM/YYYY')),-1) AS FECHAINICIO_ANTERIOR,
PERIODO_TARIFA,
VALOR_CONTRATO,
COSTO_MAYOR,
COSTO_MENOR,
PVP,
PORC_VAR,
INCLUYE,
MANUAL_TARIFARIO,
VALOR_FINAL_SERVICIO,
TIPO_DE_CONTRATO,
NIT_IPS ,
RAZON_SOCIAL AS RAZON_SOCIAL_CONTRATOS,
NOMBRE_SEDE
FROM [lib://qvdsalud (integrasavia_svcqlik)/Contratos.Qvd]
(qvd);

Left Join (La_33_integracions)

IntervalMatch(Fecha_Prestacion,LLAVE1)

LOAD FECHAINICIO,

FECHAFINAL,

LLAVE1

Resident CONTRATOS;

Left Join (La_33_integracions)

LOAD *

Resident CONTRATOS;

DROP Table CONTRATOS;

CONTRATOS_antiguos:
LOAD
NIT_IPS&'-'&COD_SERVICIO&'-'&IF(MODELO_CONTRATA='EVENTO','E','OTROS') AS LLAVE2,
COD_SERVICIO as COD_SERVICIO_CONTRATO_ANTIGUO,
CONTRATO_LEGAL as CONTRATO_LEGAL_ANTIGUO,
IF(trim(MODELO_CONTRATA)='EVENTO','E','OTROS') AS TIPO_CONTRATO_ANTIGUO,
DATE(Date#(FECHAINICIO,'DD/MM/YYYY')) AS FECHAINICIO_CONTRATO_ANTIGUO,
DATE(Date#(FECHAFINAL,'DD/MM/YYYY')) AS FECHAFINAL_CONTRATO_ANTIGUO,
MANUAL_TARIFARIO AS MANUAL_TARIFARIO_CONTRATO_ANTIGUO,
VALOR_FINAL_SERVICIO AS VALOR_FINAL_SERVICIO_CONTRATO_ANTIGUO,

NIT_IPS AS NIT_IPS_CONTRATO_ANTIGUO

FROM [lib://qvdsalud (integrasavia_svcqlik)/Contratos.Qvd]
(qvd);

Left Join (La_33_integracions)

NIT_IPS_CONTRATO_ANTIGUO,COD_SERVICIO_CONTRATO_ANTIGUO,TIPO_CONTRATO_ANTIGUO)
IntervalMatch(FECHAINICIO_ANTERIOR,LLAVE2)

LOAD FECHAINICIO_CONTRATO_ANTIGUO,

FECHAFINAL_CONTRATO_ANTIGUO,

LLAVE2
Resident CONTRATOS_antiguos;

Left Join (La_33_integracions)

LOAD *

Resident CONTRATOS_antiguos;

DROP Table CONTRATOS_antiguos;

Hello group...

I am new to QLIK,  within EHR development, primarily pharmacy services.....and want to explore some general reports, dashboards, scoreboards etc. that folks have used to get a general feel.

I see some pharmacy-specific discussions going on, and would like to see a few posters, ppt, or something that has  been developed within QLIK.

thank you
Jay Goodman R.Ph

DSS Inc.

jgoodman@dssinc.com

https://community.qlik.com/t5/Qlik-Healthcare-User-Group/How-to-Connect-QlikSense-to-a-2013-SharePoint-List/gpm-p/1478741

In reference to the above. I get the following in my connection but no data shows up. Can someone please help ?

We in Product Design Group at Qlik need your help! We are embarking on a study in which we want to update our understanding of the real user needs so that we are better equipped to focus our resources on areas where improvements are most needed.

Our first step is a survey with the purpose of identifying where the biggest struggles and delights are for people who need to get different kinds of jobs done with Qlik Sense and other data analytics solutions. The survey takes about 5-7 minutes to complete.

Here is the link to the survey:

https://forms.office.com/Pages/ResponsePage.aspx?id=X-sewqb16ESplxJPL3pJfFrGb8WQnz9Mo2pO-2HRI9NUOE1B...

Thank you for your support!