bar chart: time dimension - let user switch dim to year or month

[#1]

Hi comm,

i have the following problem.

In my bar chart i show the performance for every day, there is in the database.

so for example

day1 50%

day2 75%

day3 43%

day4 100%

and so on.

Now i want to change the dimension so be in month or even in years.

the dateformat has the years and month like YYYY.MM.DD.

So is there a way to make the Dimension be drilled up to month from the date. Like 2014.9.26 =>2014.9 => 2014.

Maybe using KPI or something.

Hope someone has an idea or a solution.

Thanks in advance

-Eric

[lironbaram]

hi

why not use , line chart (area type)

then qlik sense automatically create the functionality to drill up and down the date field .

that assuming you created for your date field a calendar using this code

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

  Month($1) AS [Month] Tagged ('$month'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

  Date(Floor($1)) AS [Date] Tagged ('$date');

DERIVE FIELDS FROM FIELDS [DATE_CLOSED],[DUE_DATE],[UPDATED_DATE],[DATE_CREATED],[Master_Date],[QA_Approval_DATE],MonthList USING [autoCalendar] ;

[#3]

Nice idea but problematic because the performance is daily based and when u draw a line it shows the wrong values between the dates u know.

so the viewer may fail interprete the visulaisation and the data provited.

Thats why i want to use barchart.

[rittermd]

Not sure this is what you are asking.  But you could setup alternative dimensions in your bar chart.  The user could then switch from daily to monthly or annual views.

[#5]

ok, did it now but seems quite user unfriendly!

I'll try to do this with a table.

[#6]

Problem with Table is that it doesn't effect the selecting of the date directly!!! can u help?

[rittermd]

Can you show me what you tried?

[#8]

I used

=month(date)

and =year(date) to build a table for selection ! But it didn't worked out

[rittermd · Accepted solution]

Did you create these in a separate table or in the existing one? 

If in the existing table then all you need to do is edit the bar chart.  Expand Data on the right.  Then under your Dimensions section click on Add Alternative.

Chose the month field and then repeat and add the year.

You have to display labels and title on the axis on the bar chart to see this option.

You should then see then see the name of the field with an arrow to click on to see the other choices.  Pick one from the list and the bar chart will regenerate based on that dimension.

Let me know how that goes.

[#10]

Thanks a lot for this tip.

And i dont need to add these month and year coloums to the table! I just add an alternativ dimension with the formular

=month (existing_date).

But i used Option to only display fieldname thats why i never saw the option.

Thanks for thsi great advise!!

-Eric