# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor III

## Creating variable for rolling sum of quantity

Hi QV experts!

Hoping to calculate to the rolling sum of actual [Quantity] divided by rolling sum of [Quantity] expected.

The date range for both numerator and denominator is the past 4 quarters (in red, I only managed to calculate these values based on a specific quarter).

My initial attempt is shown below.

sum(if([Actual Quarter]='2017-Q3' and [Sales Order Type]='Support Renewal',[Quantity]))

/

sum(if([Expected Quarter]='2017-Q3',[Quantity]))

Would it be best to create a variable for rolling quarters for each field? Or should I resort to incorporating this change in the expression?

Your help with a solution would be appreciated.

Thanks again!

Tags (2)
7 Replies
Contributor III

## Re: Creating variable for rolling sum of quantity

Hi Mona,

vLast4Quarters = QuarterStart(Max(DateField), -4)

and you can use this variable in your expression like below

sum({<[Actual Quarter]={<= \$(vlast4Quarters)}>}, [Sales Order Type]='Support Renewal'>}[Quantity])

/

sum({<[Expected Quarter]={<= \$(vlast4Quarters)}>}[Quantity]))

New Contributor III

## Re: Creating variable for rolling sum of quantity

I was able to create the variable.

However QV marks the expression as erroneous.

Could it be an error in the syntax?

Contributor III

## Re: Creating variable for rolling sum of quantity

Hi Mona,

Yes, I sent an example to show you how to place it. You can fix it by doing by adding quote in it.

Let me know if you want me to send the updated code to you.

Regards.

Valued Contributor III

## Re: Creating variable for rolling sum of quantity

it really depends on how complicated your script and UI are.

can you please share a mock-up and the output you wanted?

New Contributor III

## Re: Creating variable for rolling sum of quantity

Hi Krishna/Akshaya,

The expression that I am attempting is a little more complex for me.

I've attached a mock-up of the output as well as the expressions.

Highlighted
Contributor III

## Re: Creating variable for rolling sum of quantity

Hi Mona,

Thanks for sharing this.

Can I request you to share the dummy data too as it can help me to build expression for you.

Regards

New Contributor III

## Re: Creating variable for rolling sum of quantity

Hello Akshaya,

Unfortunately I don't have advanced editor to attached an excel file...only jpgs.