Skip to main content

New to Qlik Sense

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

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

32 Replies
sunny_talwar

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

daveamz
Creator III
Creator III

Hi Alison,

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

Ceil(MonthNumber/3)

Regards,

David

Not applicable
Author

Hi Sunny,

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

daveamz
Creator III
Creator III

Have a look at: Ceil ‒ QlikView

Is works in the same way in Sense.

Regards,

David

sunny_talwar

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?

Not applicable
Author

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:

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

Not applicable
Author

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

sunny_talwar

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

Not applicable
Author

Hi,

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