Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ahmonah2017
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!

7 Replies
adityaakshaya
Contributor III

Re: Creating variable for rolling sum of quantity

Hi Mona,

Please create a variable

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]))

ahmonah2017
New Contributor III

Re: Creating variable for rolling sum of quantity

Thanks for your response!

I was able to create the variable.

However QV marks the expression as erroneous.

Could it be an error in the syntax?

adityaakshaya
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.

Highlighted
krishna_2644
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?

ahmonah2017
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.

S&amp;IPRenewal Rates.JPG

adityaakshaya
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

ahmonah2017
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.