11 Replies Latest reply: Jun 30, 2017 4:53 AM by Alison Cooke

# Creating Quarters Field

Hello Everyone,

I am looking for some advice on how to create a quarters field.

To give you some background, I have a script that looks like this, it's pretty simple:

So, I had a number of tables which each have tables for Employee ID, Gender and Current Job Title. Each table pertain to a different month so I used the FileBaseName function to generate a field based on the name of the excel document.

Now, would it be possible to generate a field called quarters?

If you could, please provide an explanation of how your expression works, as this is just a test and I'd like to be able to apply the solution myself to other data sets.

Alison

• ###### Re: Creating Quarters Field

You could use for this:

ceil(num(mid(filebasename(), 1, 2)) / 3) as Quarter

- Marcus

• ###### Re: Creating Quarters Field

Hello Marcus,

Excellent thank you so much for helping me.

Would you mind explaining the solution?

• ###### Re: Creating Quarters Field

The month-value is divided by 3 and the result will be rounded up per ceil(), for example:

1 / 3 = 0.33 = 1

...

3 / 3 = 1 = 1

...

5 / 3 = 1.66 = 2

...

- Marcus

• ###### Re: Creating Quarters Field

Hello Again Marcus,

I am sorry but I don't understand.

Why do you want to divide the month-value by three? What is the ceil function? I have looked it up on the QlikSense website but I don't understand, it's a little advanced for me.

• ###### Re: Creating Quarters Field

Ceil is a rounding-function like round() or floor() - more could you find here: Ceil - script and chart function ‒ Qlik Sense. To divide the month-value with 3 and rounding the result is just a mathematically shortcut to avoid to query each value and assigning an equivalent value for it within a conditional function like this one:

if(Month = 1, 1, if(Month = 2, 1, if(Month = 3, 1, if(Month = 4, 2, ...) as Quarter

- Marcus

• ###### Re: Creating Quarters Field

I'm sorry but I still don't understand.

What do these individual numbers do?

1, 2)) / 3)

• ###### Re: Creating Quarters Field

1, 2) are the second/third parameter of the mid-function and mean that the function returned 2 chars beginning from the 1 sign of the string - which is the filebasename() in your case. 3 is just the fix divisor -like above described.

- Marcus

• ###### Re: Creating Quarters Field

Hi Marcus,

Thank you, I understand it a lot more now!

Please can I ask one more question of you. I am a little bit unclear about the 1,2.

Am I right to assume that they designate the months?

• ###### Re: Creating Quarters Field

It meant the second/third parameter from mid(filebasename(), 1, 2) and yes they designate your month-values retrieved from the filebasename of:

- Marcus

• ###### Re: Creating Quarters Field

Ah ok!

Thank you so much, I understand now!

• ###### Re: Creating Quarters Field

Another option could be just to leave the date field in the script and use the new(ish) autoCalendar functionality to create visualizations by quarters in the sheets view. It depends what your end goal is.