Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arethaking
Creator II
Creator II

Calculate prev month sales value if curr month sales is null

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.

Capture.PNG

4 Replies
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)


Capture.PNG

arethaking
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Please provide me script.

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