topic Show last 13 months based on current month in QlikView

Show last 13 months based on current month

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi. me again.

i have quite a few charts and KPIs but for a particular chart, i want to show the last 13 months vols only - as its currently showing all dates.

Now i have got a master calendar (which i borrowed from elsewhere), which is coded as follows, and i don't really want to mess with it, as its used elsewhere to give me date ranges etc:

MasterCalendar:

Load

TempDate AS Create_Month,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('Create_Month', recno()))-1 as mindate,

max(FieldValue('Create_Month', recno())) as maxdate

AUTOGENERATE FieldValueCount('Create_Month');

is there a way to apply a rule to a specific chart to show the last 13 months as an expression or something?

Any advice appreciated

Re: Show last 13 months based on current month

vishsaggi — Mon, 26 Sep 2016 16:56:44 GMT

Something like this ? Reference from Calculating rolling n-period totals, averages or other aggregations

= sum({<Month={'>=$(= Max(Month)-12) <=$(= Max(Month))'}>}Vol)

Re: Show last 13 months based on current month

sunny_talwar — Mon, 26 Sep 2016 17:00:33 GMT

Vish what happens when a user selects month 3. It will show >=-9<=3. I think it is better to use Date or MonthYear field to show last 12 months based on selections.

Sum({<DateField = {"$(='>=' & Date(AddYears(Max(DateField), -1), 'DateFieldFormatHere') & '<=' & Date(Max(DateField), 'DateFieldFormatHere'))"}, Month, MonthNum, Year, Quarte, Week>}Vol)

Re: Show last 13 months based on current month

vishsaggi — Mon, 26 Sep 2016 17:04:20 GMT

Oh yeah, that is correct, I restricted my thoughts on that. Thanks for the update.

Re: Show last 13 months based on current month

sunny_talwar — Mon, 26 Sep 2016 17:05:33 GMT

Not a problem man. I have been corrected for the same thing multiple times before I memorized to not make that same mistake again

Re: Show last 13 months based on current month

harishkumarg — Mon, 26 Sep 2016 17:16:28 GMT

If you are trying to display to display last 13 months data based on current month you can try the below expression,

Sum({<[DATE]={">=$(=MonthStart(Today(),-12))<=$(=MonthEnd(Today())"} >}FieldName)

Regards

Harish

Re: Show last 13 months based on current month

Anonymous — Tue, 27 Sep 2016 09:10:04 GMT

Hi - thanks for the reply.

I am still fairly new to Qlik - i'm just trying to work out where i can put this code to make it work? i assume its not in the script, but as an expression?

I attach a screen shot of the chart properties - where you will see i have create_month as the dimension, and reactive/proactive as the expression.

Just trying to understand where this code will work best?

Re: Show last 13 months based on current month

Tue, 27 Sep 2016 09:25:25 GMT

I am not sure what chart you want to show only the last 13 month, but I use a calculated dimension for such a chart.

In the data set I have Year and Month as fields and in this case I want to show last 24 full month (not current month)

=if(((MakeDate(Year,Month) < MonthStart(Today())) and (MakeDate(Year,Month) >= AddMonths(MonthStart(Today()),-24))),
Month(MakeDate(2000, Month, 1)) & ' ' & Year)

Finn

Re: Show last 13 months based on current month

vishsaggi — Tue, 27 Sep 2016 16:34:08 GMT

In your initial thread you mentioned

" i want to show the last 13 months vols only - as its currently showing all dates."

What is vols here ? Is this some kind of calculation you are doing for Reactive or Proactive ?

Re: Show last 13 months based on current month

Tue, 27 Sep 2016 16:56:19 GMT

When you use a time unit, in your case "Create_Month", as dimension in a bar chart or line chart, you can use a calculated dimension instead. If "Create_Month" contain 1, 2, 3 .. 12 and if you have a Year field containing 2015, 2016 etc. then you can use the expression in my previous reply as the calculated dimension.

You substitute the red Year, Month with your Year and Month fields adjust the -24 to -13.

If you want current month as the end month, then you substitute < MonthStart(Today()) with <= MonthEnd(Today())

The expression from my previous reply is:

=if(((MakeDate(Year,Month) < MonthStart(Today())) and (MakeDate(Year,Month) >= AddMonths(MonthStart(Today()),-24))),
Month(MakeDate(2000, Month, 1)) & ' ' & Year)

Finn

Re: Show last 13 months based on current month

Anonymous — Wed, 28 Sep 2016 10:43:31 GMT

Thank you - this worked a treat. although it is reverted the month and year in reverse order and added a column for the total volumes.

This is such a useful forum, i'm learning loads.

Re: Show last 13 months based on current month

wendytham — Tue, 17 Jul 2018 02:08:48 GMT

Hi Finn,

After using the below expression, my month sorting is not in ascending order, how to fix this problem?

Please assist. Thank you.

=if(((MakeDate(Year,Month) < MonthStart(Today())) and (MakeDate(Year,Month) >= AddMonths(MonthStart(Today()),-24))),
Month(MakeDate(2000, Month, 1)) & ' ' & Year)