topic Slowly changing dimension Day to Day comparison in QlikView

Slowly changing dimension Day to Day comparison

Mon, 17 Aug 2015 10:33:29 GMT

Hey everyone.

I've been trying to work out how to make a line chart for a few days now and I'm really stuck, I hope someone can help me.

I'm trying to show the 'Net gain' change over time.

I have a data model that includes a slowly changing dimension (Member State) and a master calendar. I define my 'Net gain' as the difference in 'Number of active members Today' VS the 'Number of active members Yesterday'.

I am also able to calculate the 'Net gain' fairly easily and place it in KPI's or pie charts. I simply use :

Count({$<Data_DATE = {'$(vMaxDate)'},STATEID ={'MEMBER'}>}EXTERNAL_ID)

Count({$<Data_DATE ={'$(vMaxDayBefore)'},STATEID ={'MEMBER'}>} EXTERNAL_ID)

This means I count the number of 'EXTERNAL_ID's (Unique person ID) which have a state of 'MEMBER' today (vMaxDate), and subtract the same calculation for yesterday (vMaxDayBefore).

However, this doesn't seem to work when I try to use this measure in a line chart, where the dimension is Data_DATE.

Am I missing something?

Shouldn't it use the results of the calculation in the measure for each Data_DATE as the value the the Y Axis?

I would appreciate any input, especially from someone who's done something similar before.

Thanks

Re: Slowly changing dimension Day to Day comparison

MK_QSL — Mon, 17 Aug 2015 10:52:29 GMT

Provide sample data please !

Re: Slowly changing dimension Day to Day comparison

Mon, 17 Aug 2015 13:34:57 GMT

I'm fairly new to the community, so let me know if you need some more data.

I'm including my QVF file, which has the data I'm testing with.

You can see an example of the view I'm trying to make called 'First Remi view'

Thanks

Re: Slowly changing dimension Day to Day comparison

Mon, 17 Aug 2015 15:36:11 GMT

Without seeing the example, I'd probably suggest loading both into 2 expressions in a straight table to make sure the values are actually being returned.

It could be a null value that's throwing it out, or an error with the dimension during load.

Re: Slowly changing dimension Day to Day comparison

Wed, 19 Aug 2015 11:21:07 GMT

Hey Graeme,

Thanks for your suggestion, I haven't tried that.

It gives me some insight as to why I'm not getting the results I want, but I'm not really sure how to fix it.

Looks like I'm getting 2 lines in the straight table.

One for the day, where I get the correct value in the 'Members on date', but 0 in the 'Members day before', and one for the day before, with the correct value in the 'Members day before', but 0 in the 'Members on date' ( I attached a screen shot).

I'm guessing I'm limiting the data set in the row by saying $<Data_DATE = {'$(vMaxDate)'}>, so I can't get in the same row the data from $<Data_DATE ={'$(vMaxDayBefore)'}> (There is no line in the Data Model that matches both criteria).

I'm not sure how to solves this though.