Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
deepaksingh
New 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.

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor II

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

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

10 Replies
OmarBenSalem
Esteemed Contributor II

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

Do u have a calendar?

deepaksingh
New Contributor III

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

Yes

pasi_lehtinen
New Contributor III

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

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
Esteemed Contributor II

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

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)

deepaksingh
New Contributor III

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

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
Esteemed Contributor II

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

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 🙂 

deepaksingh
New Contributor III

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

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

OmarBenSalem
Esteemed Contributor II

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

Can u share a testing app?

To directly work on it ?

deepaksingh
New Contributor III

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

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.