Creating variable for rolling sum of quantity

[Anonymous]

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!

[adityaakshaya]

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

[Anonymous]

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]

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.

[krishna_2644]

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

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

[Anonymous]

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.

[adityaakshaya]

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

[Anonymous]

Hello Akshaya,

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