Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Tags (2)
32 Replies

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

daveamz01
Contributor III

Re: Collating Months in Quarters

Hi Alison,

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

Ceil(MonthNumber/3)

Regards,

David

Not applicable

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?

daveamz01
Contributor III

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?

Not applicable

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:

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

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

Not applicable

Re: Collating Months in Quarters

Hi,

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

Community Browser