Solved: Take the value from previous month if not in the p... - Qlik Community

DS14 · ‎2019-01-24

Date	A	B	C	D = (A+B+C)
01-07-2018	107.73	0.17	3.45	111.35
01-08-2018	106.02	0.00	3.45	109.47
01-10-2018	107.24	0.00	3.45	110.69
01-12-2018	105.13	0.00	3.45	108.58
01-01-2019	105.14	0.00	3.45	108.59

I need the output

Date	A	B	C	D = (A+B+C)
01-07-2018	107.73	0.17	3.45	111.35
01-08-2018	106.02	0.00	3.45	109.47
01-09-2018	106.02	0.00	3.45	109.47
01-10-2018	107.24	0.00	3.45	110.69
01-11-2018	107.24	0.00	3.45	110.69
01-12-2018	105.13	0.00	3.45	108.58
01-01-2019	105.14	0.00	3.45	108.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.

OmarBenSalem · ‎2019-01-28

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)

View solution in original post

OmarBenSalem · ‎2019-01-24

Do u have a calendar?

DS14 · ‎2019-01-24

Yes

pasi_lehtinen · ‎2019-01-24

Firs you need to generate table where you have also the missing dates, which would have null values in column D. After this you can use alt(D,Previous(D) as D. Note that you need to also include necessary order by -clause into end of the load statement.

OmarBenSalem · ‎2019-01-24

Try sthing like this:

Create a chart :

as dimension :

Date (of ur calendar)

Measure:

aggr(if(sum(Measure)=0,above(sum(Measure)),sum(Measure)),Date)

DS14 · ‎2019-01-24

aggr(if(sum(Measure)=0,above(sum(Measure)),sum(Measure)),Date)

can not use this because sum(Measure)=0 work for existing month values, But I need to replace the previous month value for the missing month value

like I have the value for Jan and March, Then I have to use Jan value for the Month of Feb. In Data there is no Feb Month Exists. So I need the logic which uses the previous month values for the missing month.

OmarBenSalem · ‎2019-01-24

This what this does...

If sum(Measure) in Feb = 0, then in Feb, put the sum(Measure) of Jan (above(sum(Measure))

Try it.. at least 🙂

DS14 · ‎2019-01-28

I tried But its not working. Its adding only the existing month values. Not giving output for missing month values.

OmarBenSalem · ‎2019-01-28

Can u share a testing app?

To directly work on it ?

DS14 · ‎2019-01-28

I can't share the Qliksense application outside the office per misses but i am hereby sharing the sample data with output required. Try your logic on this dummy data Please.

Thanks for your quick response and guidance.

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

max date data

max date per month

previous month data