Lines between data points on Scatter Graph

[stevedark]

Hi,

In QlikView it is really quite simple to add a dimension to a scatter graph such that the position of points is plotted for the beginning and the end of a date range and then have a line drawn between the two points.  By altering the size of the bubble based on whether the date dim is at the start or the end, you can make it clear which value is where the historical value is and which is the most recent.  The line then gives an excellent indication of which direction a particular point is moving in.

I'm trying to recreate one of these charts in Qlik Sense, and as far as I can see it is not possible to add more than one dimension to the stock Scatter Graph.

Does anyone know of a way that I can create the visualisation like it was in QlikView?

Thanks in advance for any assistance you can give.

Steve

[stevedark]

Hi Jimmy,

I don't have a guide, but I can give you some pointers.  Reply back if you can't get it working and I will try and assist further (also reply back it it does work, so I know the instructions worked!).

First up, you need to have the Year as the first dimension on the chart, the other dimension comes second.

The Style of the scatter has to be the one with the different size bubbles third down on the right.

The expression needs to have Set Analysis to pick only the last two years, something like:

SUM({<Year={$(=max(Year)),$(=max(Year)-1)}>}Amount)

To get the grey, expand the node where the expression is and select Background Colour, the code needs to be:

=if(max(Year)=$(=max(Year)), null(), argb(25,0,0,0))

This should point you in the right direction.  Note in the colour expression the Null() will cause the chart to use the default colours, the argb gives the translucent grey.

Let me know how you get on.

Cheers,
Steve

[jimmygohhanjie]

hi ,

Thanks for helping out. the part 2 coloring works.

however part 1. Year={$(=max(Year)),$(=max(Year)-1)} this dont work out.

Mine is:

MTK_MONTH={$(=max(MTK_MONTH)),$(=max(MTK_MONTH)-1)}  --> Dont work, Error msg is 'No data to display'

MTK_MONTH={'Dec', 'Nov'} --> works

Full set anaylsis

(Round(

(sum({$<DATASOURCE_ID={'DAILY ALLOCATION'},STAFF_NAME_DISPLAY_ID-={'TEAM'},METRIC_LEVEL1_ID-={'INCOMPLETE'},MTK_MONTH={$(=max(MTK_MONTH)),$(=max(MTK_MONTH)-1)}>}EFFORT))

/

(sum({$<DATASOURCE_ID={'ATTENDANCE'},METRIC_ID={'HOURS AT WORK'},MTK_MONTH={$(=max(MTK_MONTH)),$(=max(MTK_MONTH)-1)}>}EFFORT))

,0.001))*100 &'%'

[jimmygohhanjie]

Oh,

got it.. My max(MTK_MONTH) is returning 12, instead of Dec..

will try to figure this one out..

yes, the above solutions works.

[stevedark]

When you do a max over a dual (like Month is) you will get the number rather than the text.  Try maxstring instead - I think that this may give you what you need.

I think you may have a different problem though, with regards to the year not being taken into account, it will currently give you all values for any November or December.  If you just add a max(Year) to the set analysis, this will work for all months other than comparing Jan to Dec.

The simplest way to deal with this will be to create a sequential MonthID, which you can derive like this:

((Year(MyDate)-2000)*12)+Month(MyDate) as MonthID,

My test code to generate MonthID is here:

tmpDate:

LOAD

AddMonths(MonthStart(Today()), -RowNo()) as MyDate

AUTOGENERATE(50);

MonthID:

LOAD

MyDate,

    ((Year(MyDate)-2000)*12)+Month(MyDate) as MonthID

RESIDENT tmpDate;

DROP TABLE tmpDate;

The set analysis will then work fine (as it is a number), and cope with going across years.

Steve

[jimmygohhanjie]

Hi Steve,

Your solution is good. however prefer not to amend load for this.

most likely will be using your solution for Week ID tagging.

For completeness

here is what i did.

Loading Stage

Month(Date#(MTK_DATE,'DDMMMYYYY')) as MTK_MONTH

Set vPriorMonth=month(addmonths(max(MTK_DATE),-1))

set vCurrMonth==month(addmonths(max(MTK_DATE),0))

Set Anaylsis for scatter plot. MTK_MONTH as first dimension

sum({$<MTK_MONTH={'$(vPriorMonth)','$(vCurrMonth)'}>}EFFORT))

[stevedark]

Hi Jimmy,

I may be reading the code wrong, but that still looks to me like you will have a problem if you have multiple years and want to compare Jan to the previous Dec.

Steve

[jimmygohhanjie]

Sry for the late reply.

Yes you are right..

have use MONTH_YEAR instead of just MTK_MONTH.