Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Previous Month

Hi

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(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)<1000,'800-1000',

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)<1500,'1000-1500',

''))))

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

Regards

3 Replies
oknotsen
Honored Contributor III

Re: Previous Month

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.

May you live in interesting times!
hrlinder
Honored Contributor

Re: Previous Month

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'

neetha_p
Honored Contributor

Re: Previous Month

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',

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)<1000,'800-1000',

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)<1500,'1000-1500',

''))))

Community Browser