topic Sparkline Trending Issue - Example QVW Included in QlikView

Sparkline Trending Issue - Example QVW Included

kmstephenson — Mon, 22 Jan 2018 21:24:18 GMT

Hi All,

The expression that I'm using for sparklines is incorrectly drawing the sparkline column of the table. When the numerator and denominator of this same expression is calculated using hardcoded dates in other columns of the table, these columns are correctly calculated.

See attached example for HCC Diabetes variable - the trend line in the sparkline is incorrectly depicted as follows:

As you'll see by the data in the INLINE load of the script, the correct trendline pattern is displayed by a line chart in the application and should look like this:

Notice the data is pulled in at 2 levels so that multiple indicators can be displayed on same chart (i.e. multiple chronic conditions). This example application is simplified to only include 2 patients and 2 chronic conditions but the actual application contains many more patients and conditions.

Thanks for the help!

Re: Sparkline Trending Issue - Example QVW Included

adamdavi3s — Wed, 24 Jan 2018 09:00:09 GMT

What formula did you use to generate the correct line chart?

Using your sparkline formula and dimension in a line chart generates the same output as the sparkline as expected.

Re: Sparkline Trending Issue - Example QVW Included

sunny_talwar — Wed, 24 Jan 2018 12:49:15 GMT

Try this expression

COUNT({<EndDt,QuarterEndDt-={''}>}DISTINCT KeyPatEnd)/COUNT(TOTAL <QuarterEndDt> {<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)

Re: Sparkline Trending Issue - Example QVW Included

arvind1494 — Wed, 24 Jan 2018 12:49:55 GMT

What formula did you use to generate the correct line chart?

then i will able to help you

Re: Sparkline Trending Issue - Example QVW Included

kmstephenson — Wed, 24 Jan 2018 16:14:21 GMT

Thanks, Sunny! This worked on my dummied data above but when I tried this on my actual data, trend patterns were closer but I still noticed some differences in the charts. I put together a test_sparklines_v2 QVW with dummied data that the above code DOES NOT work on - I included additional columns to compare denominators with and without the update you sent. It appears as though this code doesn't work when one of the patients doesn't have any HCC condition (i.e. either CHF or Diabetes) in the specified time, because there is no row on HCCDim table for that KeyPatEnd. Is there any way around this? Obviously, the reason for using the separate dimension and fact tables is that I want to display all of the HCC conditions (Diabetes, CHF, plus many additional not included on dummy data) on the same charts.

Thanks!

Re: Sparkline Trending Issue - Example QVW Included

kmstephenson — Wed, 24 Jan 2018 16:17:15 GMT

The line chart and straight table use data from a different structure (i.e. different variables). Here is the code in the script, showing the two table structures (but using the data values from the new QVW I posted as a response to Sunny's feedback below)

PatientFACT:
LOAD * INLINE [
    KeyPatEnd, EndDt, HCCDM, HCCCHF
    A_201312, 2013-12-31, 1, 0
    A_201401, 2014-01-31, 1, 0
    A_201402, 2014-02-28, 1, 0
    A_201403, 2014-03-31, 1, 0
    A_201404, 2014-04-30, 1, 0
    A_201405, 2014-05-31, 1, 0
    A_201406, 2014-06-30, 1, 0
    A_201407, 2014-07-31, 1, 0
    A_201408, 2014-08-31, 1, 0
    A_201409, 2014-09-30, 1, 0
    A_201410, 2014-10-31, 1, 0
    A_201411, 2014-11-30, 1, 0
    A_201412, 2014-12-31, 1, 0
    B_201406, 2014-06-30, 0, 0
    B_201407, 2014-07-31, 0, 0
    B_201408, 2014-08-31, 0, 0
    B_201409, 2014-09-30, 0, 0
    B_201410, 2014-10-31, 0, 0
    B_201411, 2014-11-30, 0, 0
    B_201412, 2014-12-31, 0, 1
];

HCCDim:
LOAD * INLINE [
    KeyPatEnd, HCCCondition
    A_201312, Diabetes
    A_201401, Diabetes
    A_201402, Diabetes
    A_201403, Diabetes
    A_201404, Diabetes
    A_201405, Diabetes
    A_201406, Diabetes
    A_201407, Dibetes
    A_201408, Diabetes
    A_201409, Diabetes
    A_201410, Diabetes
    A_201411, Diabetes
    A_201412, Diabetes
    B_201412, CHF
];

QuarterEndDt:
LOAD * INLINE [
    EndDt, QuarterEndDt
    2013-12-31, 2013-12-31
    2014-03-31, 2014-03-31
    2014-06-30, 2014-06-30
    2014-09-30, 2014-09-30
    2014-12-31, 2014-12-31
    ];

Here is the code used for the CORRECT line chart:
COUNT(DISTINCT {<EndDt, HCCDM*={1}>} KeyPatEnd)/COUNT({<EndDt>}DISTINCT KeyPatEnd)

Notice, it is using the field from the PatientFACT table, i.e. the indicator for HCCDM. The reason why I wanted to create HCCDim separate table with one row per HCC indicators set to 1 is because I'd like to display all HCC condition indicators on the same charts (i.e. compare %s and trends for each condition).

Re: Sparkline Trending Issue - Example QVW Included

kmstephenson — Wed, 24 Jan 2018 16:19:48 GMT

QuarterEndDt:
LOAD * INLINE [
    EndDt, QuarterEndDt
    2013-12-31, 2013-12-31
    2014-03-31, 2014-03-31
    2014-06-30, 2014-06-30
    2014-09-30, 2014-09-30
    2014-12-31, 2014-12-31
    ];

Here is the code used for the CORRECT line chart:
COUNT(DISTINCT {<EndDt, HCCDM*={1}>} KeyPatEnd)/COUNT({<EndDt>}DISTINCT KeyPatEnd)

Re: Sparkline Trending Issue - Example QVW Included

sunny_talwar — Wed, 24 Jan 2018 16:26:59 GMT

Try this

COUNT({<EndDt,QuarterEndDt-={''}>}DISTINCT KeyPatEnd)/Only({<EndDt,QuarterEndDt-={''}>}Aggr(NODISTINCT COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd), QuarterEndDt))

Re: Sparkline Trending Issue - Example QVW Included

kmstephenson — Wed, 24 Jan 2018 17:49:19 GMT

This looks really good. I'll validate and then mark as correct! Do you also know whether I'll need to update my code to calculate in columns the Minimum Quarterly Rolling Year, Minimum Quarterly RY Date, Maximum Quarterly RY, and Maximum Quarterly RY Date? You helped with these in a separate post but now that I have updated sparkline code, I'd like to create the same columns that display the minimum % highlighted in the sparkline, the associated date, and the maximum % highlighted in the sparkline, and the associated date?

I was using this code before made the change to sparkline code:

Minimum Quarterly RY:

Min({<EndDt,QuarterEndDt-={''}>}Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL <QuarterEndDt>{<EndDt,QuarterEndDt-={''}>}DISTINCT KeyPatEnd), QuarterEndDt,"$(vDimensionConditionTrends)"))

Minimum Quarterly RY Date:

FirstSortedValue({<EndDt,QuarterEndDt-={''}>} QuarterEndDt, Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL <QuarterEndDt>{<EndDt,QuarterEndDt-={''}>}DISTINCT KeyPatEnd), "$(vDimensionConditionTrends)",QuarterEndDt))

Maximum Quarterly RY:

Max({<EndDt,QuarterEndDt-={''}>}Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL <QuarterEndDt>{<EndDt,QuarterEndDt-={''}>}DISTINCT KeyPatEnd), "$(vDimensionConditionTrends)",QuarterEndDt))

Maximum Quarterly RY Date:

FirstSortedValue({<EndDt,QuarterEndDt-={''}>} QuarterEndDt, -Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/COUNT(TOTAL <QuarterEndDt>{<EndDt,QuarterEndDt-={''}>}DISTINCT KeyPatEnd), "$(vDimensionConditionTrends)",QuarterEndDt))

Thank you so much! Will validate and mark as correct!

Re: Sparkline Trending Issue - Example QVW Included

sunny_talwar — Wed, 24 Jan 2018 17:53:15 GMT

May be try this

Minimum Quarterly RY Date:

FirstSortedValue({<EndDt,QuarterEndDt-={''}>} QuarterEndDt, Aggr(COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd)/Only({<EndDt,QuarterEndDt-={''}>}Aggr(NODISTINCT COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd), QuarterEndDt)), "$(vDimensionConditionTrends)",QuarterEndDt))

So essentially replace this

COUNT(TOTAL <QuarterEndDt>{<EndDt,QuarterEndDt-={''}>}DISTINCT KeyPatEnd)

with this

Only({<EndDt,QuarterEndDt-={''}>}Aggr(NODISTINCT COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd), QuarterEndDt))

in all your expressions

Re: Sparkline Trending Issue - Example QVW Included

kmstephenson — Wed, 24 Jan 2018 18:23:01 GMT

This results in missing values for all of these columns. I've added this code to the attached file - these columns are not populated. Any minor change I'll need?

Thank you for all of your help!

Re: Sparkline Trending Issue - Example QVW Included

sunny_talwar — Wed, 24 Jan 2018 18:33:55 GMT

Three problems

1) You missed a closing parenthesis for Only() function

Only({<EndDt,QuarterEndDt-={''}>}Aggr(NODISTINCT COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd), QuarterEndDt))

2) You have a variable which is not even created in the sample app... so I guess this is probably not going to be an issue for you

vDimensionConditionTrends is not available

3) If there are more than one month with Max/Min value, what would you want to show? Randomly any? If Random is good, add DISTINCT after FirstSortedValue.

Re: Sparkline Trending Issue - Example QVW Included

kmstephenson — Wed, 24 Jan 2018 18:41:56 GMT

Ahhh! Good catches! See my responses and fixes below!

1) You missed a closing parenthesis for Only() function - DONE!

Only({<EndDt,QuarterEndDt-={''}>}Aggr(NODISTINCT COUNT({<EndDt,QuarterEndDt-={''}>} DISTINCT KeyPatEnd), QuarterEndDt))

2) You have a variable which is not even created in the sample app... so I guess this is probably not going to be an issue for you - Updated the sample app to use the appropriate field for the dummied data!

vDimensionConditionTrends is not available

3) If there are more than one month with Max/Min value, what would you want to show? Randomly any? If Random is good, add DISTINCT after FirstSortedValue. - I would like to show the most recent date when there is more than one month with the min or max... how would I do this? See attached _v4.

Re: Sparkline Trending Issue - Example QVW Included

sunny_talwar — Wed, 24 Jan 2018 18:48:19 GMT

Check now

Re: Sparkline Trending Issue - Example QVW Included

kmstephenson — Wed, 24 Jan 2018 18:57:35 GMT

This is perfect! Thank you!!!