Creating Quarters Field

[#1]

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.

Thank you in advance,

Alison

[marcus_sommer · Accepted solution]

You could use for this:

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

- Marcus

[#3]

Hello Marcus,

Excellent thank you so much for helping me.

Would you mind explaining the solution?

[marcus_sommer]

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

[#5]

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.

[marcus_sommer]

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

[#7]

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

What do these individual numbers do?

1, 2)) / 3)

[marcus_sommer]

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

[#9]

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?

[marcus_sommer]

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

- Marcus