topic Re: Previous Month in QlikView

Previous Month

Fri, 14 Aug 2015 09:59:00 GMT

Below is my caluclated Dimension which works for current month.

=If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<600,'0-600',

If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<800,'600-800',

''))))

If i want to do it for Previous month , how can i do that

Regards

Re: Previous Month

oknotsen — Fri, 14 Aug 2015 10:03:51 GMT

This is one hell of a calculated dimension.

Are you sure you can not do this in script instead? Calculated dimensions are not good for performance. Setting a flag in script might make this even a lot easier to do.

Re: Previous Month

Anonymous — Fri, 14 Aug 2015 10:04:56 GMT

add the function addmonths around your current month definition

now. Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD'

new for previous month: addmonths(Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD',-1)

or use inner Definition Date(addmonths(MonthStart(Max(ALT1)),-1), 'YYYY-MM-DD'

Re: Previous Month

Anonymous — Fri, 14 Aug 2015 10:14:30 GMT

maybe try like this please:

=If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<600,'0-600',

If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<800,'600-800',

''))))