topic Re: Offsetting Dates in QlikView

Offsetting Dates

richard_pearce6 — Tue, 22 Sep 2015 09:12:50 GMT

Just wanted to check to see if anyone has encounter this before an knows of an elegant solution before I spend too much time trying to engineer one

I have a simple data table with Dates and Stock Volumes The chart needs to use date as a dimension and sum the Stock Volumes (Easy so far)

The customer wants a slider to control a date offset. -1 Day -2 Days, etc

So the results which would have shown as:

16-Sep SV 100

Now shows as:

14-Sep SV 100 if the user changes the date offset to -2

Basically all the dates shift back two. Now, I had thought about using a calculated dimension (=Date(Date-2,'DD-MMM-YYYY') ) as a simple cheat and hide the actual Date dimension column that’s driving the calculations. This would work although I have other numbers in the chart that don’t want to be affected by this offset and need to show the actual date, worst case would be to have two charts which maybe won’t be an issue although if it’s a simple trick that I just can’t think of right now I’d rather not split them.

Ideally it should be done with Set Analysis. Has anyone encountered this before? Any insight would be appreciated.

Also, just to note I've tried using the Below function (=below(Sum([SV]),vOperationDateOffset) ). Unfortunately this only steps back on the possible date values for SV. So in the example earlier it won't step back two days but two possible lines for that expression. I think it maybe some combination of this and aggr() but not too sure.

Cheers

Richard

Re: Offsetting Dates

marcus_sommer — Tue, 22 Sep 2015 10:31:34 GMT

If the max. offset isn't very big it could be that additionally date- and/or flag-fields are useful for those calculations. Another point which I would try out if a dual() dimension couldn't be helpful. I mean to display other dimension-values as used for the calculations, something like:

dual(Date(Date-2,'DD-MMM-YYYY'), Date)

or reversed (maybe you could avoid with such approach your below-expression).

- Marcus

Re: Offsetting Dates

richard_pearce6 — Wed, 23 Sep 2015 10:24:25 GMT

Thanks Marcus. the Offset should be only a few days. Probably a spread of ten at the most so I see where you're coming from with adding additional columns.

Ideally though I'd like it to be truly dynamic if possible although I starting to get the feeling it may not be possible

Richard

Re: Offsetting Dates

marcus_sommer — Wed, 23 Sep 2015 10:52:13 GMT

It's difficult to say then even if it would be possible to include all the needed challanges into one chart - if it's then practically and fast enough (I had have already managed to really slow down very small applications with heavily nested dimension/expression-constructs - not to mention the efforts to build and maintain it) so that two (or more) objects might be a better solution.

- Marcus

Re: Offsetting Dates

richard_pearce6 — Wed, 23 Sep 2015 12:39:11 GMT

Yes I agree Marcus.

Data size is small so performance wouldn't be an issue which is why I wanted to explore the option.

I have split the charts for now as it was never a requirement. Its always interesting to know if some of these things are in fact possible.

Cheers