Month wise last 3 month Sales Analysis in Line chart

[marishnagendran]

Hi all,

I wanted to show the trend for last three months average sales for the current fiscal year,

For Example

In April'18 -> Last 3 month Sales(Jan,Feb,Mar(2018))

May'18 -> Feb,Mar,Apr(2018)

Jun'18 -> Mar,Apr,May (2018)

I have tried using the expression:

RangeAvg(Above(Sum({<FiscalYear = {'$(=Max(FiscalYear))'}>}kpi_sales_qty)/1000,0,3))

Above expression, doesn't help me because it includes the current month. 

Like, 

Apr'18 -> Feb,Mar, Apr (2018)

May'18 -> Mar,Apr, May (2018)

Jun'18 -> Apr,May, Jun (2018)

Regards,

Marish

[marishnagendran]

Yeah got your point..

PFA

[pradosh_thakur]

What are the codes is it month? If not how do you know which month has what value?

You have fixed the calculation for 

FiscalYear = {'$(=Max(FiscalYear)'}

 That's why the the first row is becoming zero. if you change the second parameter of above() from 1 to 0 i think you will see the output in the first row itself.

Thanks

Pradosh

[marishnagendran]

Yeah Correct I know that,But the query is for April month (i.e id 1) i want to show previous fiscal year month (i.e March,feb,Jan 2018) Sales in April month bar,For May month (Feb,Mar,Apr) Sales etc...

[sunny_talwar]

Where is Month used in this chart? Is Code = Month?

[marishnagendran]

Yes...

[sunny_talwar]

So, I am seeing 16,734.42 in RangeAvg column where Code = 4... which is just the average of 16,268.56, 17,173.35, and 16761.34 which are Jan, Feb and Mar months.

So, I guess what you are saying is that in the table RangeAvg is showing the correct number which is 16,734.42... but not in the bar chart? Am I understanding the issue correctly here?

[marishnagendran]

PFA,I want this logic

[sunny_talwar · Accepted solution]

Got it... try this

If(RowNo() < 4,
Sum({<FiscalYear = {'$(=Max(FiscalYear))'}>}kpi_sales_qty)/1000,
RangeAvg(Above(Column(1), 1, 3))
)

[marishnagendran]

Thanks Sunny great.Its Working