Rolling 12 months sales (in script)

[wendytham]

Hi..

I have 2 questions here.

1. I would like to know how to create a new variable in the script for the below formula.

Market ratio:

(Number of jobs of current month x 12 months) / (total sales unit of last 12 months).

I wanted to exclude the job with part#=no_parts. I wonder why the below equation only workable in expressions of chart but not in the script. Please assist to check if the below formula is correct.

Count(DISTINCT {<Part# -= {'No_Parts'}>} Job#)*12/sum( aggr( rangesum( above( sum( [Sales Qty]),0,12) ),(Monthname,(NUMERIC, ASCENDING))))

2. If the Financial Year period is as below, and I would like to select the year by Financial Year, how to set it?

04/2016 to 03/2017 = FY2016

04/2017 to 03/2016 = FY2017

Please help me. Thank you.

[Anil_Babu_Samineni]

Expression seems Okay, Where you want to call with Variable initiating?

May be this for first one?

1) LET Variable = "=(Count(DISTINCT {<Part# -= {'No_Parts'}>} Job#)*12)/(Sum( aggr( rangesum( above( sum( [Sales Qty]),0,12) ),(Monthname,(NUMERIC, ASCENDING)))))";

Check that in Variable declaration to text box for a while and see, Whether returns or not

2) Fiscal Year

[wendytham]

Hi Anil,

Sorry, for the first 1, it doesn't work. Can you show me in qvw file?

Thanks.

[Anil_Babu_Samineni]

I apologies doe to not helping with workfile due to i don't have installed software in my machine.

[wendytham]

It's okay. Anyway, thanks for your help.