Calculate prev month sales value if curr month sales is null

[arethaking]

Hi All,

We have the data until Jul-2016.

Filter Conditions:

Year=2016,Month=Sep,Flag='A',ProductGroup='VXS FD N1'=Sales=0

But I want to show Jul Sales 96 to Sep and Aug as shown below

Year=2016,Month=Sep,Flag='A',ProductGroup='VXS FD N1',Sales=96.

Year=2016,Month=Aug,Flag='A',ProductGroup='VXS FD N1',Sales=96.

Because there is no sales for Aug and Sep for the above conditions.

How to write the script or front.

Expression:  =sum({<Flag={'A'},ProductGroup={'VXS FD N1'}>}Sales)

Script:

DataTemp:

load Product,ProductGroup,Sales,Flag,date#(Month,'MMM-YY') as Month,Date#([Month Name],'MMM') as  [Month Name],year(date#(Year,'YYYY')) as Year;

NoConcatenate

Data:

LOAD Product,ProductGroup,Sales,Flag,Month,[Month Name],Year,

  if(len(trim(Sales))=0,Previous(Sales),Sales) as New_Sales

Resident DataTemp

Order By Product,ProductGroup,Flag,Month,[Month Name],Year; //or Order By COUNTRY, PRODUCT asc/desc;

DROP TABLE DataTemp;

Kindly help me.

Please find the sample data and app.

[sunny_talwar]

May be this:

=Num(Sum(TOTAL {<Year={$(=Max(Year))}, MonthName = {"$(='>=' & Date(Min(MonthName), 'MMM-YY'))"}, Flag={'A'}, ProductGroup={'VXS FD N1'}, Month>} Sales),'##,##0') +

Avg({<MonthName = {"$(='>=' & Date(Min(MonthName), 'MMM-YY') & '<' & Date(MonthStart(Today(), 1), 'MMM-YY'))"}, Month, Date>} 0)

[arethaking]

Hi Sunny,

Thanks for the reply.

If I select Sep, it should show Sep month with the value of Jul

If I select Sep, it should show Aug month with the value of Jul

Because Sep,Aug value is null and these two months are previous month.

Thanks in advance.

[sunny_talwar]

Why don't you fix this in the script so that you don't have to manipulate this on the front end?

[arethaking]

Please provide me script.

I want to show in a line chart and a text box.