How do I minus 12 months from a DATE?

Fri, 30 May 2014 06:02:54 GMT

Hi,

I want to count the number of activities performed in an Account within 12 months from the accounts expiry period. I have a field date called Renewal and my thought was to use that and minus 12 months from it - but unsure on how to do this. My current expression below gives me everything and I have to use a calendar slider to select the right data - as below:

count ({<Mandatory = {'1'}, Delivered = {'*'} - {'0'}, DeliverDate -= {'0'}>}distinct Account & CSP_Name)

I also have the following Master Calender if that helps:

MasterCalendar:
Load

TempDate AS Renewal,

week(TempDate) As EndWeek,

Year(TempDate) As EndYear,

Month(TempDate) As EndMonth,

Day(TempDate) As EndDay,

YeartoDate(TempDate)*-1 as EndCurYTDFlag,

YeartoDate(TempDate,-1)*-1 as EndLastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as EndRC12,

date(monthstart(TempDate), 'MMM-YYYY') as EndMonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as EndQuarter,

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

WeekDay(TempDate) as EndWeekDay

Thanks for any help.

Cheers.

Re: How do I minus 12 months from a DATE?

SergeyMak — Fri, 30 May 2014 06:07:41 GMT

The simple way is use function AddMonths(ourDateField, -12)

But I don't understand clearly your requirements

Re: How do I minus 12 months from a DATE?

nirav_bhimani — Fri, 30 May 2014 06:08:43 GMT

Hi,

Try this Expression,

Count({<Mandatory = {'1'}, Delivered = {'*'} - {'0'}, DeliverDate -= {'0'},CalDate={">$(=addyears(max(CalDate),-1))<=$(=max(CalDate))"},CalYear=,CalMonth=,CalQuarter=>}distinct Account & CSP_Name)

Regards,

Nirav Bhimani

topic Re: How do I minus 12 months from a DATE? in QlikView

How do I minus 12 months from a DATE?

Re: How do I minus 12 months from a DATE?

Re: How do I minus 12 months from a DATE?