topic Re: Dates issue revisited in QlikView

Dates issue revisited

Thu, 13 Sep 2012 09:57:19 GMT

Hi all,

I wondered if anyone had come up with a better solution for the problem of visualising data with dates on a chart.

The situation I'm talking about is where you want to show data for a time period, e.g. a week, where you want Monday-Sunday displayed but you only have data for Mon, Wed, Thurs. Obviously, this problem is extended to days of the year, months etc...

Normally we create a calendar table that doesn't link, using this date as the dimension and then an expression like:

SUM ( IF(trans_date = calendar_date, SALES))

This works fine for smaller data sets. When the data set grows and the calculations become more complex, an IF statement here that essentially forces a join is VERY slow and renders some applications unusable.

This is something that I want to do all the time. In this example I would never want a chart that just showed Mon, Wed, Thurs, I would want to display every day of that week but only have bars (if it were a bar chart) on the days where there were sales.

If anyone has a slick, well performing solution I'd love to hear it!

Thanks

Gareth

ps - i realise this has been discussed a lot but to my mind no usable solution has been found for larger data sets.

Re: Dates issue revisited

Thu, 13 Sep 2012 10:10:48 GMT

I would suggest you a master calendar. this is how solved this problem and it also works with a lot of data very well.

http://community.qlik.com/qlikviews/1001 i used this one and modeled it for my needs.

Hope this helps.

regards,

Re: Dates issue revisited

Thu, 13 Sep 2012 10:20:50 GMT

I appreciate the link.

Unfortunately, this works because the data has data for every day in the calendar. If you knock out a day (e.g. 05/08/10) and then create a chart, the axis simply skips from 05/07/10 to 05/09/10. I want to, in this scenario, have 05/08/10 present on the x-axis but with no bar showing.

Hope that makes sense?

Thanks.

Gareth

Re: Dates issue revisited

Thu, 13 Sep 2012 10:28:40 GMT

yes you make sense, but thats what the mastercalendar is for to have every date. i need a lil bit more input for you problem i might can help you out.

What dimensions has your chart?

What formular are you using?

What is you selection?

regards,

Re: Dates issue revisited

Thu, 13 Sep 2012 11:10:07 GMT

Yes I understand that every date is in the master calendar, so I use this date as my dimension.

Say I use an expression (on that QVW) of :

Count( [Order Number])

Then for the day I have knocked out (05/08/10) I have no data so I want a blank space, instead I get the date of 05/07/10, 05/09/10... so 05/08/10 is missing.

I try selection 'show zero values' and 'show all values' but this doesn't give me the desired result.

Does that make sense?

Thanks

Gareth

Re: Dates issue revisited

Thu, 13 Sep 2012 11:12:57 GMT

See my chart I have created in this calendard document. No 05/08/10 present and I can't get it to show on the chart.

Kind regards,

Gareth

Re: Dates issue revisited

Thu, 13 Sep 2012 11:33:55 GMT

ok...in the sheet presentation you can able and unable null-values excluded (smth like that Im not using qv in english)

the only problem now is that you see now all dates. but you can use a listbox for the month or year smth like that.

hope this helped a lil bit.

regards,

Re: Dates issue revisited

flipside — Thu, 13 Sep 2012 11:39:34 GMT

Try adding 0.01 to the expression and setting the number format of the expression to integer. You should get something like this ...

... you can even set the little bar to white to hide it by using an expression in Background Color to ...

if(count([Order Number])=0,rgb(255,255,255))

flipside

Re: Dates issue revisited

flipside — Thu, 13 Sep 2012 11:45:57 GMT

Actually just selecting Continuous on the Axes tab should do what you need.

Re: Dates issue revisited

Thu, 13 Sep 2012 12:12:16 GMT

Thanks flipside and kbs,

This seems to be working a treat.

Re: Dates issue revisited

Thu, 13 Sep 2012 12:38:02 GMT

Actually this has created a second problem. In that same QV document, if you create a second expression (for example):

COUNT ( [Order Number]) *1.2

If the axis is set to continuous then the bars ovelap completely and one series is essentially hidden behind the other one. The 'Bar Settings' aren't available under properties, so how do I get it to look like a discrete axis again while remaining essentially continuous?

Any ideas?

Thanks

Gareth

Re: Dates issue revisited

flipside — Thu, 13 Sep 2012 13:47:51 GMT

You may have to go back to my original idea of adding a small number to the sum and forgetting the continuous checkbox.

Another idea would be to do something similar in the script ...

SampleRange:
LOAD min(CalendarDate) as rangeSt, max(CalendarDate)-min(CalendarDate) as rangeCount resident SampleData;

let x = peek('rangeSt',0);

TmpRangeData:
LOAD
Date($(x) + RowNo()-1) as tmpCalDate,
0.0001 as Details
autogenerate peek('rangeCount',0)+1;

right join (SampleData)
load tmpCalDate as CalendarDate, Details as OrderOverride resident TmpRangeData;

This just adds a new column to the data filling in the blanks between earliest and latest dates. All you have to do then is use this in an expression like this ...

=numMax(count([Order Number]),OrderOverride)

flipside

Re: Dates issue revisited

Thu, 13 Sep 2012 16:09:39 GMT

Thank you I can see that this workaround will work in practice, but it is less than ideal.

It's a shame that Qliktech didn't foresee that people would want to include dates in this way and make it a simple procedure.

If anyone else has a slick way of achieving this I'm really keen to hear about it.

Cheers

Re: Dates issue revisited

Thu, 13 Sep 2012 17:02:08 GMT

Ditto. I'm having the exact same issue. We're still on qv9 at the moment, but i wonder if v10 or v11 have fixed this issue?

Re: Dates issue revisited

flipside — Thu, 13 Sep 2012 18:32:06 GMT

Definitely the same in v10.

You can do something like this using Continuous and split axes ...