Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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

View solution in original post

10 Replies
Highlighted
Partner
Partner

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

Do u have a calendar?

Highlighted
Partner
Partner

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

Yes

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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)

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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 🙂 

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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 ?

Highlighted
Partner
Partner

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.