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

[pradosh_thakur]

My be this

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

[marishnagendran]

Hi Pradoshthakur,

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

If i used above expression it takes April month Sales shown in May month,But requirement is 

2018 - April Month -> I want to take last 3 month sales average(Jan,Feb,Mar) and it shown in April Month

2018 - May Month -> Feb,Mar,Apr Sales average shown in May month

Note : We are using fiscal year and 5 year of data (2013,2014,2015,2016,2017,2018) 

[sunny_talwar]

How is your data sorted? Is it ascending order or descending order? If it is in descending order, may be you need this

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

If it is in ascending order, I don't see why @pradosh_thakur expression won't work

[marishnagendran]

Hi Sunny talwar,

It Won't Work, It shows fully null value

[sunny_talwar]

Can you attach an image or a sample to see what you have?

[marishnagendran]

In Apr Month Bar, I want to show Last 3 month sales (Jan,Feb,Mar) as new expression, Same as all the months

[sunny_talwar]

I guess it would be easier for me if you can create a table instead of a bar chart. and add two expressions

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

and

Sum({<FiscalYear = {'$(=Max(FiscalYear))'}>}kpi_sales_qty)/1000

[marishnagendran]

Yeah in table it's easier,But our client ask this to shown in Bar chart..

[pradosh_thakur]

@marishnagendran  What sunny was asking was to create a table with the same expression and show us the output, he was not asking to change it to table, We can see the other values and see the expression as well , so it will be easier  for us in helping you out.