List Table Sparkline vs Line Chart

[MarkJones]

Hi,

I have been using sparklines in a list table in Visual Analytics 7.3 but I've noticed that they don't seem to tell entirely the same story as a line chart of the same data.

The line chart and sparkline below have been drawn using exactly the same data (which is attached in the text file in CSV form - the website doesn't allow uploading files with a .csv extension -  if anyone would like to try and replicate this):

I can see that the line chart shows a slight decline in the rate of increase towards the end of the line, however this gets hugely exagerated in the sparkline.  I'm guessing this is something to do with some smoothing that is going on for the sparkline, however the VA user guide doesn't mention anything about the methodology behind the sparkline.  A manager would likely have a small heart attack looking at the sparkline, so it's pretty much unusable in this form.

I also cannot work out what the numbers in the sparkline data tip represent.  It can be clearly seen from the line chart that the measure never exceeds much over 2000 so I am stumped where 1308, 62198, 18034 come from.  The aggregration in use for the measure is SUM, and as far as I can work out these numbers don't represent any form of that aggregation.

Does anyone have any ideas what might be going on before I go to Tech Support?

Kind regards,

Mark

[MichelleHomes · Accepted solution]

Hi Mark,

You "sparked" my interest 🙂

It seems the sparkline figures are based on monthly sum aggregation. From your sparkline_data.csv file the sum aggregation of the following months form the figures 1308, 62198, 18034.

1st month - Jan-13 - sum of Number of members is 1308

2nd last month - Jan-16 - sum of Number of members is 62198

last month (Final) - Feb-16 - sum of Number of members is 18034

I suspect that the drop in the sparkline is because the month of February is incomplete and it may be drawing the line to the average of February which is 2004. I wonder if you change your aggregation to average it becomes more meaningful with monthly averages being reported in the tool tip and the spark line for February being as expected.

Hope this helps.

Cheers,

Michelle

[MarkJones]

Hi Michelle,

You are a genius (at puns too )  - thank you very much!  I had started tearing my hair out over that one. 

I'm going to suggest to SAS that they mention this in the user documentation as it would have taken me ages to figure out that it was aggregating on a monthly basis, to me at least this was far from obvious.

Changing the aggregation to average certainly improves things, but the data tip is still confusing until you specify a somewhat long-winded name for the measure:

I also wonder if the basis for the aggregation depends on the range of the time axis.  Logically it would depend on whether a date, time or datetime item is used, but what if the range of my data was months or even more years.  Something to investigate.

Either way sparklines just became a bit more complex to explain to Report Designers.

Thanks again Michelle.

Mark

[MichelleHomes]

Hi Mark,

My pleasure... was fun to explore!

Agree, some clarity on the sparkline for different time scales would be good.

Cheers,

Michelle

[MarkJones]

I have added this suggestion to the SASware ballot, please feel free to vote it up everyone!

https://communities.sas.com/t5/SASware-Ballot-Ideas/Visual-Analytics-List-Tables-Sparkline-documenta...

[MichelleHomes]

Good idea Mark in adding it to the SASware Ballot!

I've placed my vote too.

Cheers,

Michelle

[MarkJones]

A further follow up to this issue.

The eagle-eyed may spot that my sparkline using averages, whilst looking better the figures in the data tip still did not tally with the line chart.

The line showed figures around 2000 for February 2016 whereas the line chart says the current average is just 334:

Once I spotted this even my mental arithmetic is good enough to know that (9 * ~ 2000) / 9 is not equal to 334!

I eventually figured out what is going on.

The source data I provided earlier wasn't quite complete, it was aggregated up to the Gym ID and date level which was all I needed to illustrate my question.  My full data set (attached) is finer grained than this, including a couple of other categories namely member type (3 levels) and gender (2 levels) so rather than there being one record per date there are actually 6 records per date. 

My mistake was thinking that when I used this fine grained data at a higher level of aggregation (Gym ID / month rather than Gym ID / member type / gender / month) that the average aggregation would take this into account rolling up both the numerator and the denominator to this level, but it does not.  The denominator is always the number of non-missing values, so for 1st - 9th February there were a total of 54 observations (9 dates * 3 member types  * 2 genders) so the average was calculated as the sum of the measure divided by 54, instead of the sum divided by 9 as I had wanted.

In most situations once this is understood it's not a big problem, you'd just need to add a new Aggregated Measure to give the average of the measure _ByGroup_.  Unfortunately this is not one of those situations as you cannot use an Aggregated Measure in a sparkline.  As such the only solution I can see to my problem is to have a seperate LASR table (or maybe one artfully created LASR table) containing data for each grain that I want to present the sparkline at.

Hope this helps some of you avoid the same trap I nearly fell into.

Mark