32 Replies Latest reply: Jun 29, 2017 6:57 AM by Sunny Talwar

# Collating Months in Quarters

Hello All,

I have a number of tables which have been concatenated. I have then used the FileBaseName() function to retrieve the Excel filename and, thus, identify the month the data pertains to.

Would it be possible to organise this data into a quarter? If so, please let me know.

All the best,

Alison

P.S

If it's possible, please can provide a description of how the solution works as if I am just provided with the function, I find it difficult to understand how and why it works.

• ###### Re: Collating Months in Quarters

In general you can use a master calendar script to do this

Creating A Master Calendar

But for specific requirement, you can use this logic

'Q' & Ceil(Month(MonthYearField)/3) as Quarter

• ###### Re: Collating Months in Quarters

Hi Sunny,

Please can you provide a more wordy explanation. What are the various functions in your solution and what do you do?

• ###### Re: Collating Months in Quarters

Have a look at: Ceil ‒ QlikView

Is works in the same way in Sense.

Regards,

David

• ###### Re: Collating Months in Quarters

So basically 'Q' is just the text and concatenate the Quarter number. Quarter number is calculated as follows

Divide Month by 3 and then get the ceiled value for example Ceil(1/3) = Ceil(0.3333) = 1,

Ceil(4/3) = Ceil(1.3333) = 2. So show the next closest integer here.

Does it make sense?

• ###### Re: Collating Months in Quarters

Hi Alison,

If you know the month number (1, 2, 3....12):

Ceil(MonthNumber/3)

Regards,

David

• ###### Re: Collating Months in Quarters

Is it possible to take it back a stage as I am struggling.

I am trying to calculate the earliest and the latest date using the max and min function.

So the script I'm writing looks like this:

MinMax:

Min([MM-YYYY]) AS MinDate,
Max([MM-YYYY]) AS MaxDate
Resident [(Sheet1...)];

However, when I load it and go to the UI the fields are available but they do not generate any values.

Please can you help me with this?

• ###### Re: Collating Months in Quarters

I will just pose this as a separate question as it may be easier

• ###### Re: Collating Months in Quarters

Sounds like a plan, but is this question resolved? If it is, I would encourage you to close the thread by marking correct and helpful responses.

Best,

Sunny

• ###### Re: Collating Months in Quarters

Hi,

I know that that's advisable but I haven't found a solution as of yet.

• ###### Re: Collating Months in Quarters

But didn't you pose another question here? Is this related to your initial question? I am confused? Can you clarify what is the issue you are still running into?

• ###### Re: Collating Months in Quarters

Sorry for the confusion.

I have now been able to produce the min and max values. But I am still struggling with the master calendar. I think I need to rephrase my question.

• ###### Re: Collating Months in Quarters

This thread was related to Quarters... where did Min and Max came from? I am completely confused right now.... Have no idea what you want

• ###### Re: Collating Months in Quarters

I thought identifying the min and max was a prerequisite of generating quarters? Is it not?

• ###### Re: Collating Months in Quarters

Min and Max are probably the pre-req for creating a calendar, but it is not a pre-req for calculating quarters.

• ###### Re: Collating Months in Quarters

Ah ok, thank you for letting me know.

Do you not need a master calendar to calculate quarters?

• ###### Re: Collating Months in Quarters

Do you not need a master calendar to calculate quarters?

Not really....

• ###### Re: Collating Months in Quarters

Ok thank you for letting me know

• ###### Re: Collating Months in Quarters

No problem, but where do we stand with regards to this thread? Is this resolved or what else do you need to resolve your issue

• ###### Re: Collating Months in Quarters

Sorry for all the confusion, I was confused about the question I was asking.

So I suppose I was right the first time, I want the months to be collated into quarters. Thank you for your patience!

• ###### Re: Collating Months in Quarters

Did you try this?

'Q' & Ceil(Month(MonthYearField)/3) as Quarter

I explained what it is doing... does the explanation doesn't make sense?

Re: Collating Months in Quarters

• ###### Re: Collating Months in Quarters

You can even use Mapping load to find Quarters

• ###### Re: Collating Months in Quarters

Thank you for you numerous responses. Please can you tell me, where should I place this expression?

• ###### Re: Collating Months in Quarters

In the script where you have Month field

• ###### Re: Collating Months in Quarters

I haven't defined the month field.

I have only have a field that I've generated from the File Base Name, which is called MM-YYYY and holds that value. It is potentially not being recognised as a numeric value which might be causing trouble.

• ###### Re: Collating Months in Quarters

If you don't mind sharing, can you show how exactly are you using FileBaseName() function to get MM-YYYY?

• ###### Re: Collating Months in Quarters

Yeah no problem. I loaded a number of tables which share field names and so auto-concatenated. Each table pertains to a different month and so I created a field name out of the excel file name. Below is replicated for each table:

[Sheet1_d9c0e355-4eb4-8421-c01b-49f204a2]:
[Gender],
[Current Job Title],
mid(FileBaseName(), 1, 8) as [MM-YYYY]

FROM [lib://Demo/01-2017 Organisation Name .xlsx]
(ooxml, embedded labels, table is Sheet1);

RENAME TABLE [Sheet1_d9c0e355-4eb4-8421-c01b-49f204a2] TO [Sheet1];
RENAME TABLE [Sheet1] TO [(Sheet1...)];

• ###### Re: Collating Months in Quarters

Try this

[Sheet1_d9c0e355-4eb4-8421-c01b-49f204a2]:
[Gender],
[Current Job Title],
Mid(FileBaseName(), 1, 8) as [MM-YYYY],

'Q' & Ceil(Month(Date#(Mid(FileBaseName(), 1, 8), 'MM-YYYY'))/3) as Quarter
FROM [lib://Demo/01-2017 Organisation Name .xlsx]
(ooxml, embedded labels, table is Sheet1);

RENAME TABLE [Sheet1_d9c0e355-4eb4-8421-c01b-49f204a2] TO [Sheet1];
RENAME TABLE [Sheet1] TO [(Sheet1...)];

• ###### Re: Collating Months in Quarters

For example, if you are doing this today

SubField(FileBaseName(), '_', -1) as Month

Then you can do this for the Quarter

'Q' & Ceil(Month(Date#(SubField(FileBaseName(), '_', -1), 'MM-YYYY'))/3) as Quarter

• ###### Re: Collating Months in Quarters

I had tried to be clear in my initial post but I then become unsure of what question I should be asking, so sorry the lack of clarity.

The expression to generate month is working fine so thank you for that.

However, the Quarters expression is not working correctly. When in the UI the field generates no values.

Additionally, I would really appreciate it is you could explain the how the expression works as then, hopefully, I won't have to come back to you with the same issue in the future.

Nevertheless, thank you for all your help so far.

• ###### Re: Collating Months in Quarters

Perhaps things got confused, sorry about that. As such I opened a new thread. Is it possible to close this one as it's not much use to anyone.

• ###### Re: Collating Months in Quarters

As sunny said in his first comment, try adding master calender in your script, all your dates related issues will be resolved.