Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Display All Calendar month even if there is no data for the month

Hi, I have a table with some data. All data have  a creation date field. So i display in a table all my data create by month with the creation date with a count.

                         Jan. 2011     Feb. 2011     Mar. 2011 .     ...      Nov. 2011

CountCreate:          2                    8               12              ...          6

But for exemple; there is no data in December or in April; So the table will not display the month in the table. But i would like to display it even if there is no data.

                         Jan. 2011     Feb. 2011     Mar. 2011 .     ...      Nov. 2011     Dec. 2011

CountCreate:          2                    8               12              ...          6                    0

Is it possible to do that in the table and the chart ?

Thanks by advance

1 Solution

Accepted Solutions
Not applicable

Re: Display All Calendar month even if there is no data for the month

I got it ...

In fact, i replace: =if(month([Date création DCP]), MMYYYY) by MMYYYY

and

I unchecked:

     - Delete if nul values (in Dimentions)

     - Delete nul values (in Presentation)

     - Delete missed Values (in Presentation)

     - Display all values (in Dimentions)

that's all

10 Replies

Re: Display All Calendar month even if there is no data for the month

HI

Do you have dec 2011 in that month field? if so, use like this

untick suppress zero value in presentation tab.

untick suppress when value is null in dimension tab.

Hope it helps

rajeshvaswani77
Valued Contributor III

Re: Display All Calendar month even if there is no data for the month

Create a Calandar tab amd Use a script like this

Range:

Load min(Link_Master_Calendar) as StartDate,

     max(Link_Master_Calendar)  as EndDate

Resident Table1;

Let vSTART = peek('StartDate',-1,'Range')-1;

Let vEND = peek('EndDate',-1,'Range');

Let vRANGE = $(vEND) - $(vSTART);

Let vTODAY = num(date(today()));

Drop Table Range;

Dates:

Load $(vSTART)+recno() as Date

AUTOGENERATE $(vRANGE);

Calendar:

Load dATE(Date,'dd/MM/YYYY') as Link_Master_Calendar,

dATE(Date,'dd/MM/YYYY')      as FullDate,

Year(Date)                   as Year,

Month(Date)                  as Month,

Week(Date)                   as Week,

'Q'&ceil(Month(Date)/3)      as Quarter,

Month(Date)&'-'&right(Year(Date),2) as [Month Year]

Resident Dates;

Drop Table Dates;

Finally change Link_Master_Calendar to your actual date fields.

Not applicable

Re: Display All Calendar month even if there is no data for the month

Thanks for help.

I tried that already it didnùt works. And yes my calendar is complete...

Not applicable

Re: Display All Calendar month even if there is no data for the month

I'm using a special calendar for the script because i have to take the 15 days of prev month to the 15 days of the actual month. So in my script i have:

  In the code file cause i can't put the script here

Thanks

Not applicable

Re: Display All Calendar month even if there is no data for the month

up please

vaibsonly
Contributor II

Re: Display All Calendar month even if there is no data for the month

hi guillaumek,

                  

                        can u ellaborate more....coz as per i cud understand.....u can make use of loosely coupled .....

regards,

Vaibhav

Not applicable

Re: Display All Calendar month even if there is no data for the month

I found a part of the question. In fact i checked "Display all the values" in dimention options of the table.

But it display me all the values of the calendar, like even if i make a selection on 2012 value, I will have 2006 - 2007 ....

So i made a calculated dimention: =if(year([Date création]), MMYYYY)

MMYYYY = Month + year.

But it display me only the data if there is a creation date for the month. So i have nothing in december in creation date, so i won't have december in my table.

And I want to have december even if it's displayed with value 0.

Do you have an idea?

vaibsonly
Contributor II

Re: Display All Calendar month even if there is no data for the month

hi guillaumek,

              

                     As per i can understand- u can do d following-

                     suppose u have atable wit values and dates..... suppose 2000 = 10,2001=14,2003=15.

                     now u have a calender which have dates 2000,2001,2002,2003,2004,2005..

                     so u want to see in chart as :

                     Dates     Values

                     2000        10

                     2001         14

                     2002         0

                     2003         15

                     2004          0

                     2005          0

                     now to get d above

                     1.-  dont link table wit calender date( kip both separate)

                     2 -  in dimension u take calender dates and in expression  - if (cal=table date, values)

                     3 - uncheck mark - from presentation tab - suppress zero values and suppress missing and null symbol  give '0' ans missing symbol give '0' instaed of '-'

    tatz it

i hope diz shud work......

regards,

Vaibhav

Not applicable

Re: Display All Calendar month even if there is no data for the month

Yes i think it's what i have:

 

Calendar

MasterCalendar:

LOAD * INLINE [Date création] ;

JOIN(MasterCalendar) LOAD

     [Date création],

     Month,

     Year,

     letterMonth,

     MMYYYY

RESIDENT TempMasterCalendar;

DROP TABLE

TempMasterCalendar;

And the only one link with my data table is [Date création] ;

I have to have this for the charts and tables, for doing a relation with them.

in the table where there is [Date création] ;, I have some other information.

So it's like if [Date création] would be the "Values" you say I think.

I have a calculated dimention:

  =if(month([Date création DCP]), MMYYYY)

I unchecked:

     - Delete if nul values (in Dimentions)

     - Delete nul values (in Presentation)

     - Delete missed Values (in Presentation)

I checked:

     - Display all values (in Dimentions)

So do you have an idea please? did i make a mistake?

Community Browser