topic Re: Master Calender + line chart in QlikView

Master Calender + line chart

Wed, 05 Feb 2014 12:58:18 GMT

Hi all,

I have a problem with my master calender and my line chart. I have a master calender for a couple of years and i created a line chart that shows me sum(profit) for each year and calenderweek and weekday. calendarweek and weekday are one dimension like cw & ' ' & wd.

everything is showing correctly except on dates where i dont have a profit value. meaning on the first of may over here it is always a holiday so we have no profit. but my dimension is always calendarweek + weekday so so for example calenderweek 17 Monday was one in 2009 the first of may and in 2010 ist was the second of may(has profit) so my lines are not showing 0 for 2009 it is jsut being ignored and jumps automaticly to the next date.

can someone help me how i can fix this?

my tables are:

Master claender with the columns Date, Weekday, Week, year, month

and the table:

movement with the columns Date (connection to master calender) , profit

and movement has only lines for the dates where we had profit

regards,

Re: Master Calender + line chart

MK_QSL — Wed, 05 Feb 2014 13:06:50 GMT

Go to presentation tab and untick Supress Zero Values

Re: Master Calender + line chart

datanibbler — Wed, 05 Feb 2014 13:08:08 GMT

Hi Magdalena,

it is a bit tricky and you will have to do a bit of trial-and-error here:

=> It is quite hard to get Qlikview to display something that is not available.

- That said, the next important questions to ask yourself would be:

=> What format is your base_data?

=> Do you have a field for that day and simply no value in it (blank field)

or is there no field available for that day?

=> How is your fact_table (for the chart) linked to the calendar?

=> Which fields are you using as dimension? Those from the fact_table itself or from the calendar

(the fact_table might not have a field for that day, but the master_calendar should, no?)

=> You might need a "dummy formula" (hard-coded as 0) just to force QlikView to display the chart_area when there
is no value to display.

HTH

Best regards,

DataNibbler

Re: Master Calender + line chart

Wed, 05 Feb 2014 13:15:00 GMT

ok the dimensions in the line chart are from my master calender. so that shouldnt be the issue right?? and my master calender are linked through the date field (_DATE) how can I create dummy values??

When i use a table object and I select a date wich has no profit it still schows a line in movement with only a value in the date field

Re: Master Calender + line chart

ashwanin — Wed, 05 Feb 2014 13:32:20 GMT

as your Calender is directly linked with Dates in Table, so it must reflect the value 0. Because you are already creating a dummy date.

Is problem arises only with the Month start or it reflects on all the days where you don't have values of Profit.

Re: Master Calender + line chart

Wed, 05 Feb 2014 13:39:13 GMT

if i create a textbox and sum(profit) for the selected date it shows me 0 but in my line chart i dont get the 0 values displayed.

the dimension on the x-achis is week + weekday. so on 18 Mi für the year 2013 I should have 0.

Re: Master Calender + line chart

juleshartley — Wed, 05 Feb 2014 13:45:47 GMT

Sorry, but have you unchecked the 'suppress where value is null' as suggested above?

Re: Master Calender + line chart

martinpohl — Wed, 05 Feb 2014 13:46:50 GMT

Hi!

The line chart will only show you the 0 if there is a value on this date. Otherwise the line will go from monday to wednesday.

Add on formula tab at presentation "points" to see that there is a value.

Regads

Martin

Re: Master Calender + line chart

Wed, 05 Feb 2014 13:49:41 GMT

ok but how can i create this 0 instead of null for that date? cause where in the database there is no profit for this day.

and yes suppress when value is null is unchecked

Re: Master Calender + line chart

MK_QSL — Wed, 05 Feb 2014 13:52:10 GMT

Check enclosed file...

To make NULL to 0...

try below..

IF(ISNULL(Sales), 0, Sales) as Sales

But in your database there should not be any NULL values as the Dates themselves are not exists...!!!

Re: Master Calender + line chart

martinpohl — Wed, 05 Feb 2014 13:53:54 GMT

Create a value of dates in a single table.

Join 0 as the value.

concatenate the table where not exists the date.

Re: Master Calender + line chart

MK_QSL — Wed, 05 Feb 2014 13:54:17 GMT

Re: Master Calender + line chart

ashwanin — Wed, 05 Feb 2014 13:55:45 GMT

Zero Values,

Make sure you got a calendar with all the months you want to display.
Enable Continuous dimension axis
Uncheck the Suppress Zero-Values under Presentation properties.