How to Accumulates Sales Qty Rolling Starting From April to March

[Tofanz]

Hi All,

I have a requirement to accumulate sales qty starting from April to March in our fiscal year.

So far i tried to create a formula but still did not get what i want because the value starting from January.

this is my formula:

Sum(Aggr(RangeSum(Above(TOTAL Sum({<
[FiscalMonth]=
>}
QTY_W_SALES),0,RowNo(Total))),[FiscalMonth]))

This is the result :

 how can i get the proper formula to have "accum" value start summarizing in April not in January ?

regarding our fiscal year starting in April.

Thank You for your help,

Tofanz

[Anil_Babu_Samineni]

Please change below variable from 

SET FirstMonthOfYear=1;

to

SET FirstMonthOfYear=4;

[Tofanz]

Dear Anil,

Thank for your reply.
but i already did that in my Data load editor. as you can see in FiscalMonth column, April already become the first Month in the fiscal year.

but i still haven't figure it out why the formula in "Accum" measurement starting in January

[Sameer9585]

Can you please tell me the of january means example one entry,

[Tofanz]

Dear Sameer,

i'm sorry if my question and explanation didn't clear regarding this is my first time to post a question in this forum and my writing in english is very poor.

My current issue is I want accumulate Sales Qty in Fiscal year Starting April 2018 - March 2019.

Unfortunately my formula just see the first Month of the common Year date which is Start from January - December. and the result become Jan 2019 - Dec 2018.

and my goal is like this pict below:

[Gysbert_Wassenaar]

Try sorting the aggr by FiscalMonth:

Sum(Aggr(RangeSum(Above(TOTAL Sum({<[FiscalMonth]=>}QTY_W_SALES),0,RowNo(Total))),FiscalMonth], ([FiscalMonth], (Numeric, Ascending)) ))

[Sameer9585]

ok try this?
Pick(Match(FiscalMonthName, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'),'10','11','12','1','2','3','4','5', '6', '7','8','9') and name this as Month and then apply formula as
Sum(Aggr(RangeSum(Above(TOTAL Sum({< Month=>}QTY_W_SALES),0,RowNo(Total))),Month))

[Tofanz]

Dear Gysbert,

i already try your way but still had same result

[sunny_talwar · Accepted solution]

Have you tried just this

RangeSum(Above(TOTAL Sum({<[FiscalMonth]>} QTY_W_SALES), 0, RowNo(TOTAL)))

[Tofanz]

Dear Sameer,

I'm trying your way but still confuse how do I put the pick function regarding i'm new in qliksense