Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master III
Master III

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!
Anonymous
Not applicable
Author

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'

Anonymous
Not applicable
Author

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

''))))