Collating Months in Quarters

[#1]

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.

[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]

Hi Alison,

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

Ceil(MonthNumber/3)

Regards,

David

[#4]

Hi Sunny,

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

[daveamz]

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?

[#7]

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?

[#8]

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

[#10]

Hi,

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