Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Only show last 18 months

I am trying to create a line graph with using the expression sum(Cash_on_hand) and the dimension MonthYear.

I need my graph to only show the last 18 months of data, how do I do this.

Thanks in advance.

1 Solution

Accepted Solutions
Highlighted

Sorry, I made some mistakes in the expression. Try now (hopefully no more mistakes)

Sum({<MonthYear= {"$(='>=' & Date(MonthStart(Max(MonthYear), -18), 'MM-YY') & '<=' & Date(MonthStart(Max(MonthYear), 0), 'MM-YY'))"}>}Cash_on_hand)

View solution in original post

10 Replies
Highlighted

May be like this for your expression:

Sum({<Date = {$("= '>=' & Date(MonthStart(Max(Date), -18), 'DateFieldFormatHere') & '<=' & Date(MonthStart(Max(Date), 0), 'DateFieldFormatHere')"}>}Cash_on_hand)

Highlighted
Partner
Partner

is this keeping in mind that my Dimension in MonthYear (a combination of month and year like 08-16)

because it does not seem to be wokring

Highlighted

Are you using set analysis with date or MonthYear? How is your MonthYear field getting calculated in the script?

Highlighted
Partner
Partner

No set analysis in the monthyear. I think that is all i need. it the proper set analysis for my dimension "MonthYear" to only show the Max -18.

Highlighted

How is MonthYear field created in the script

Highlighted
Partner
Partner

MinMax:

LOAD

Min([Date]) as MinDate,

today()-1 as MaxDate

Resident

[Rev_DD];

Let vMinDate = Num(Peek('MinDate',0,'MinMax'));

Let vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

Let vToday = num(Today());

drop Table MinMax;

//Autogenerate a source table for your calendar

TempCalendar:

Load

$(vMinDate) + RowNo() - 1 as Num,

Date($(vMinDate) + RowNo() - 1) as TempDate

Autogenerate

$(vMaxDate) - $(vMinDate) + 1;

//Generate the Master Calendar

MasterCalendar:

Load

Autonumber('|'&Date(TempDate)) as DateNum,

//This Next line determins what links the [Main Data] Table and the [Master Calander] table

Date(Floor(TempDate)) as [Date],

Date(WeekStart(TempDate),'M/D/YYYY') as WeekStart,

Date(WeekStart(TempDate), 'M/D/YY') as WeekStart2,

Week(TempDate) as Week,

Year(TempDate) as Year,

chr(39) & right(Year(TempDate),2) as [Short Year],

Month(TempDate) as Month,

MonthStart(TempDate) as MonthStart,

Year(Floor(TempDate)) &

  If(Len(Num(Month(Floor(TempDate))))=1, 0 &

  Num(Month(Floor(TempDate))),

  Num(Month(Floor(TempDate)))) as Period,

Num(Month(TempDate), '00') as MonthNum,

Day(TempDate) as Day,

WeekDay(TempDate) as WeekDay,

'Q' & ceil(Month(TempDate)/3) as Quarter,

'Q' & ceil(Month(TempDate)/3)&'-'&

  Right(Year(TempDate),2) as QuarterYear,

Date(monthstart(TempDate),'MM-YY') as MonthYear,

autonumber(Date(monthstart(TempDate),'MMM-YY')) as MonthYearID,

Week(TempDate) & '-' & Right(Year(TempDate), 2) as WeekYear,

Right(Year(TempDate), 2) & '-' & Week(TempDate) as YearWeek,

autonumber(Right(Year(TempDate), 2) & '-' & Week(TempDate)) as YearWeekID,

inyeartodate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

inyeartodate(TempDate,$(vToday),-1) * -1 as LastYTDFlag,

if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,

if(date(TempDate) >= Date(Today(0)-7), '1') as Rolling7Day,

if(date(TempDate) = Date(Today(0)-2), '1') as PreviousDay,

if(date(TempDate) >= Date(Today(0)-90), '1') as RollingAvg

Resident TempCalendar

Order By TempDate ASC;

//Delete temp table

Drop Table TempCalendar;

exit SCRIPT;

Highlighted

Try this:

Sum({<MonthYear= {$("= '>=' & Date(MonthStart(Max(MonthYear), -18), 'MM-YY') & '<=' &Date(MonthStart(Max(MonthYear), 0), 'MM-YY')"}>}Cash_on_hand)

Highlighted
Partner
Partner

Sunny,

This is not working. I am not getting any values if I use that as my expression

Highlighted

Sorry, I made some mistakes in the expression. Try now (hopefully no more mistakes)

Sum({<MonthYear= {"$(='>=' & Date(MonthStart(Max(MonthYear), -18), 'MM-YY') & '<=' & Date(MonthStart(Max(MonthYear), 0), 'MM-YY'))"}>}Cash_on_hand)

View solution in original post