How to generate months that are not there in data model

[ashis]

Hi ,

I have 6 months of data in my data model , however I want to show all the months in a list box. How to get it.

I have created a master calendar and generating months (and dates) using min date and max date  in my data model and all the dates in between these min and max date.

Now I have created one inline table and put all the months from Jan to Dec , and concatenate this inline table with the master

calendar. But my problem is when I create list box I can see duplicate months which are available in my data model.

It seems to me it is a formatting issue but not able to figure out the problem.

In my transaction table date is in this following format 01 Jan 2017 00:00:00

Please help.

Thank you,

Ashis

[Sergey_Shuklin]

Hello!

Use master calendar for whole year and concat it with data model dates.

There is no need of creating an additional inline table.

[its_anandrjs]

I believe do not concatenate inline table with months because they are strings and in your calendar they are pure dates due to this they display duplicates.

[ashis]

Hi Anand,

If I created date island or month island , then I need to associate it in all the charts that I have and that would be very big task at this moment.

I am looking for other alternatives. Is there anything I  could do.

Thank you,

[ashis]

Hi Sergey,

I am using master calendar and generating date and month with the help of data that I have in transaction table.

whatever min date I have in transaction table and max date in transaction table , it is generating date  accordingly.

Can you tell me how do I generate those months that are not in my transaction table .

My date is associative in with master calendar ( that means , my master calendar is not data island) .

Any suggestion.

[prieper]

Changing the format does not help, you need to have a descriptive table with the aggregation of the day-values into months, years etc.

As dirty trick you may simple re-assign the dimensions:

Calendar:

LOAD

MyDate                    AS MyDate_exact,

MONTHNAME(MyDate)     AS MyDate,

...

[pradosh_thakur]

May be this

load month(abc) as Month;

load * inline

[abc

'01-21-2017'

'02-21-2017'

'03-21-2017'

'04-21-2017'

'05-21-2017'

'06-21-2017'

'07-21-2017'

'08-21-2017'

'09-21-2017'

'10-21-2017'

'11-21-2017'

'12-21-2017'

];

no need to join or concatenate. Let Qlik auto associate by [Month]. use Month in a list box and it shall show all the value. Let me know if it works for you.

note: My date format is 'MM-DD-YYYY' .check yours and apply,

regards

Pradosh

[Peter_Cammaert · Accepted solution]

A MasterCalendar typically extracts the minimum date and maximum date from your facts table. To get all months in a year, whatever the dates in your facts table, add YearStart() to the extraction of the minimum date, and YearEnd() to the extraction of the maximum date. Then generate the MasterCalendar. For example:

MinMaxTable:

LOAD YearStart(Min(OrderDate)) AS MinDate,

     YearEnd(Max(OrderDate)) AS MaxDate

RESIDENT Orders;

:

Even if the minimum and maximum dates are the same (because of having only one row in your facts), you will still have a MasterCalendar with at least all 12 months in it.

Best,

Peter

[ashis]

Thank you, it worked .