topic date range in X Axis Dimension by selected one date in QlikView

date range in X Axis Dimension by selected one date

stevegimbrollmt — Fri, 06 May 2011 14:06:44 GMT

I have a line chart and I want that when i select lets say 15-04-2011, in the line chart, I actually get a range from 15-03-2011 to 15-04-2011 rather than showing just one date selected.

I have tried doing something with the calculated dimension but seems its not workign and also the Min/Max Scale expression only work for the Y Axis.

Is this possible?

Thanks!

Steve

date range in X Axis Dimension by selected one date

Fri, 06 May 2011 14:14:31 GMT

You would probably be better off using variables than actual selections. Set up one variable called like vMinDate and one called vMaxDate. Make an input box for vMaxDate, and in the variable overview set vMinDate to: =date(vMaxDate-1)

Then in the line chart expression, use set analysis like this:

[code]

{$<[Date]={">=$(vMinDate) <=$(vMaxDate)"}>}

[/code]

Or you could not set vMinDate to anything and have an input box for both vMinDate and vMaxDate.

Re: date range in X Axis Dimension by selected one date

johnw — Fri, 06 May 2011 22:58:50 GMT

I'd probably just stick with fields instead of variables. It's very much the same basic idea, though - use set analysis to pretend you selected more dates than you actually selected. Something like this, maybe:

{<[Date]={">=$(=date(min(Date)-1) <=$(=date(max(Date))))"}>}

date range in X Axis Dimension by selected one date

stevegimbrollmt — Mon, 09 May 2011 10:05:14 GMT

thanks for your feedback guys, im gona try it now, sorry for late reply but got stuck on something else, but from

Trent feedback: I understand what you mean but not sure if its exactly what I want but will try it out.

John: thanks as always, to be honest I tried something like that, not sure if the modifier was wrong but will try your sample.

date range in X Axis Dimension by selected one date

stevegimbrollmt — Mon, 09 May 2011 10:32:18 GMT

hmmm John not sure if I did this correct but in the chart expression I have put this expression

if(GetSelectedCount(Day)> 1,Sum(actual_turnover),Sum({<[created_date]={'>=$(=date(min(created_date)-30))<=$(=date(max(created_date)))'}>} actual_turnover))

My aim is that if I select just 1 day, I want to see a date range of 30 days, if not, it just shows the selected days.

Is this possible?

Thanks guys!!

date range in X Axis Dimension by selected one date

johnw — Mon, 09 May 2011 18:29:57 GMT

Double quotes, not single quotes. Other than that it looks right to me, but I could be missing something.

date range in X Axis Dimension by selected one date

stevegimbrollmt — Tue, 10 May 2011 06:20:48 GMT

Thanks for your help. It still not working as in, it still shows me selected day only rather than a range of 30 days on the axis.

I need to find a way how when I select 30-03-2011, on the graph i see displayed dates 01-03-2011,02-03-2011...etc etc till 30-03-2011.

Will need to find a way to do it. Thanks again John

date range in X Axis Dimension by selected one date

stevegimbrollmt — Tue, 10 May 2011 12:05:03 GMT

John, your answer was correct,

I just found out what I was doing, so let me explain why it did not work immidiately

So currently I have this expression - if(GetSelectedCount(Day)> 1,Sum(actual_turnover),Sum({<[created_date]={'>=$(=date(min(created_date)-30))<=$(=date(max(created_date)))'}>} actual_turnover))

Now the created_date = is being selected by selected a [Day], [Month], [Year] which are assiciated to the created_date HOWEVER, when I add the created_date in the report and I actually selected a particualr date, the chart displays the dates, if I select the day,month and year seperately even though they are associated to the created date, the report will only display one day.

So now I am going to find a way how to make it work with the [Day], [Month], [Year]

Thanks!

Re: date range in X Axis Dimension by selected one date

johnw — Tue, 10 May 2011 18:39:56 GMT

Ah, yes, you need to use set analysis to ignore every other date-related field associated with created_date. This is normally done by listing them explicitly:

if(GetSelectedCount(Day)> 1,Sum(actual_turnover),Sum({<[created_date]={">=$(=date(min(created_date)-30))<=$(=date(max(created_date)))"},Day=,Month=,Year=>} actual_turnover))

There's also a way to tell QlikView "ignore every field in the calendar table except for created_date", but you'd probably reserve that for if there are a lot of fields in the table to be ignored.

Re: date range in X Axis Dimension by selected one date

stevegimbrollmt — Tue, 10 May 2011 21:46:58 GMT

IMPRESSIVE!

It worked, am doing more testing but it worked. so when in set analysis you do Field=, that means, exclude those fields im selecting?

Thanks once again John

Re: date range in X Axis Dimension by selected one date

johnw — Tue, 10 May 2011 21:54:06 GMT

SteveGImbroll wrote:
so when in set analysis you do Field=, that means, exclude those fields im selecting?

Yep, that's what it means. Ignore the selections in that field. Now, it's not exactly a perfect ignore, as it will take into account how those fields affect values in other fields that you may be referencing. But I think that's what we want here, in that if you select a day, month and year, you WANT it to use the matching created_date when calculating your 30-day spread.

Thinking a little further, I'd think you'd want your if() to be if(created_date,...), though. I'm guessing that "Day" is the day of the month. In and of itself, that's not enough, as you'd also need to select a month and a year. But I'm thinking that doesn't really matter so much as just you've "selected" a single created_date, which you can check by just seeing if it has a value. If it has a value, then only one value is possible.

date range in X Axis Dimension by selected one date

stevegimbrollmt — Tue, 10 May 2011 21:58:31 GMT

yes I have to agree, now that I'm understanding more how it is being interpretted I shall revise my conditions but for this particular need for now its doing the job, but def will fine tune it!