Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
DS14
Partner - Contributor III
Partner - Contributor III

Take the value from previous month if not in the present in current month

DateABCD = (A+B+C)
01-07-2018107.730.173.45111.35
01-08-2018106.020.003.45109.47
01-10-2018107.240.003.45110.69
01-12-2018105.130.003.45108.58
01-01-2019105.140.003.45108.59

 

I need the output

DateABCD = (A+B+C)
01-07-2018107.730.173.45111.35
01-08-2018106.020.003.45109.47
01-09-2018106.020.003.45109.47
01-10-2018107.240.003.45110.69
01-11-2018107.240.003.45110.69
01-12-2018105.130.003.45108.58
01-01-2019105.140.003.45108.59

 

If the value  is not available for any of the month then it should take the previous month max (Date) value for that particular month.

Please guide me to achieve this.

Labels (3)
10 Replies
OmarBenSalem

Table:
first 5
LOAD
"Date",
A,
"B",
C,
"D = (A+B+C)",
F
FROM [lib://DATA_SOURCES/cost logic.xlsx]
(ooxml, embedded labels, table is COST);


Temp:

Load

min("Date") as minDate,

max(Date) as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

 

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear

 

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

 

iN PRESENTATION:

Dimension : MonthYear

Measure:  aggr(if(Sum([D = (A+B+C)])=0, above(Sum([D = (A+B+C)])),Sum([D = (A+B+C)])),MonthYear)

Capture.PNG