How To Create a Master Calendar and Calendar Field

[Kenji_Masicat]

Hi guys,

I’m trying to create a Calendar measure but was unsuccessful.

The prompt says "NO DATE FIELD".

I tried to revise my table at data manager but to no avail.

Can you help me on this please? What are the steps I need to take to create a calendar field for my data shown below?

Also, how can I create a calculated field to compute for MTD & YTD Growths?

[anthonyj · Accepted solution]

Hi @Kenji_Masicat ,

I noticed in your screenshots that you don't have a date column that includes day, month and year. From what I can see the "Create calendar measures" function requires a date field not just a Period or Year.

If you have a full date field then can I suggest adding this. If you don't you might have to create a calculated date field based off your available columns.

Regards

Anthony

[Kenji_Masicat · Accepted solution]

Thank you Anthony. Yes, my data doesnt have a date column and is already arranged in Period (Month) and Year.

There is no date field as the data are final monthly numbers. Would you mind teaching me how to create a calculated date field? 🙂 

[anthonyj · Accepted solution]

Hi @Kenji_Masicat ,

Your month is a text so you'll need to get Qlik to interpret the 3 letter month name. This will take the values in your "Year" and "Period" columns and append the "1" to make a date field that is the first of the month.

makedate(Year,  num(month(date#(Period,'MMM'))) , 1) as fullDate

If you want to represent the date as the end of the month:

monthend(makedate(Year, num(month(date#(Period,'MMM'))), 1)) as MonthEndDate

I hope this helps.

Thanks

Anthony

[Kenji_Masicat]

Thanks a million @anthonyj. This offers a lot of help. Will check and will get back to you. 🙂

[Kenji_Masicat]

Hi @anthonyj , here's the prompt that I got. Apologies because Im just new to QLik sense

Thank you.

Kenji

[anthonyj · Accepted solution]

Hi and no worries at all @Kenji_Masicat. We were all there at one stage.

The code that I gave you needs to go into the table where the columns "Year" and "Period" are coming from. It doesn't look like it's the table being read in that I can see behind your log as I can't see those column names. Must be on a different tab. 

For example:

Data:

load

Year,

Period,

monthend(makedate(Year, num(month(date#(Period,'MMM'))), 1)) as MonthEndDate

from YourData;

Let me know how you go and if it doesn't make sense.

Thanks

Anthony

[Kenji_Masicat]

Thanks a million @anthonyj for taking may call and for teaching me how to fix my outstanding concern. Your gesture is highly appreciated. Will take all the learnings and share to our team. 🙂

[Kenji_Masicat]

Hi Anthony, it's me again. I revised my period & year columns to Order Date column and had the format DDMMYYYY . 

 As such, I got auto generated Calendar tables in the fields.

[Kenji_Masicat]

Question is, what will be the expression for the Master Items I created for YTD Gwt?

I created this expression & shows error.