topic Re: Creating variable for rolling sum of quantity in QlikView

Creating variable for rolling sum of quantity

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Creating variable for rolling sum of quantity

adityaakshaya — Mon, 30 Apr 2018 18:04:17 GMT

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

Re: Creating variable for rolling sum of quantity

Anonymous — Mon, 30 Apr 2018 21:52:56 GMT

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?

Re: Creating variable for rolling sum of quantity

adityaakshaya — Tue, 01 May 2018 14:51:15 GMT

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.

Re: Creating variable for rolling sum of quantity

Qrishna — Tue, 01 May 2018 15:11:26 GMT

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

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

Re: Creating variable for rolling sum of quantity

Anonymous — Tue, 01 May 2018 21:19:43 GMT

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.

Re: Creating variable for rolling sum of quantity

adityaakshaya — Tue, 01 May 2018 21:42:11 GMT

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

Re: Creating variable for rolling sum of quantity

Anonymous — Tue, 01 May 2018 22:56:28 GMT

Hello Akshaya,

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