
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- « Previous Replies
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In general you can use a master calendar script to do this
But for specific requirement, you can use this logic
'Q' & Ceil(Month(MonthYearField)/3) as Quarter

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Alison,
If you know the month number (1, 2, 3....12):
Ceil(MonthNumber/3)
Regards,
David

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
Please can you provide a more wordy explanation. What are the various functions in your solution and what do you do?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I will just pose this as a separate question as it may be easier

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I know that that's advisable but I haven't found a solution as of yet.

- « Previous Replies
- Next Replies »