Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
Kenji_Masicat
Contributor III
Contributor III

How To Create a Master Calendar and Calendar Field

Hi guys,

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

KenjiMasicat_4-1630025067302.jpeg

The prompt says "NO DATE FIELD".

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

 

KenjiMasicat_2-1630024947704.jpeg

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?

KenjiMasicat_3-1630024947748.jpeg

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

4 Solutions

Accepted Solutions
anthonyj
Creator II
Creator II

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

 

View solution in original post

Kenji_Masicat
Contributor III
Contributor III
Author

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? 🙂 

View solution in original post

anthonyj
Creator II
Creator II

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

View solution in original post

anthonyj
Creator II
Creator II

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

View solution in original post

9 Replies
anthonyj
Creator II
Creator II

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

 

View solution in original post

Kenji_Masicat
Contributor III
Contributor III
Author

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? 🙂 

View solution in original post

anthonyj
Creator II
Creator II

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

View solution in original post

Kenji_Masicat
Contributor III
Contributor III
Author

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

Kenji_Masicat
Contributor III
Contributor III
Author

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

KenjiMasicat_0-1630377242222.png

Thank you.

 

Kenji

anthonyj
Creator II
Creator II

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

View solution in original post

Kenji_Masicat
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

Kenji_Masicat_2-1630981268758.png

 

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

Kenji_Masicat_3-1630981268760.png

 

 

Kenji_Masicat
Contributor III
Contributor III
Author

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

I created this expression & shows error. 

Kenji_Masicat_6-1630981321153.png

 

Kenji_Masicat_7-1630981321156.png