0 Replies Latest reply: Nov 9, 2016 6:47 AM by m ellis

Summing up  for 3 different periods

I have  non-standard set analysis/if statement issue:

I want to sum compare a  first of the month target date with  all activity within the same month to compare: I have tried the following with no success using the PITR Calendar :

[Master Calendar]:

Year & MonthNo  as PeriodNo,

AutoNumber(Year & MonthNo, 'MonthID') as MonthID,

Month & '-' & Year  as Period,

'Q' & Ceil(MonthNo / 3) as Quarter,

AutoNumber(Year & 'Q' & Ceil(MonthNo / 3), 'QuarterID') as QuarterID;

Date(\$(vDateMin) + IterNo() - 1, '\$(DateFormat)')  as Date,

Day(\$(vDateMin) + IterNo() - 1) as DayNo,

WeekDay(\$(vDateMin) + IterNo() - 1) as WeekDay,

Year(\$(vDateMin) + IterNo() - 1)  as Year,

Month(\$(vDateMin) + IterNo() - 1)  as Month,

Num(Month(\$(vDateMin) + IterNo() - 1), '00')  as MonthNo,

WeekYear(Date(\$(vDateMin) + IterNo() - 1, '\$(DateFormat)')) & '-' &

Num(Week(Date(\$(vDateMin) + IterNo() - 1, '\$(DateFormat)')), '00') as [YearWeek],

Date(MonthStart(\$(vDateMin) + IterNo() - 1), 'YYYY-MM') as YearMonth,

AUTOGENERATE 1

WHILE \$(vDateMin)+IterNo()-1<= \$(vDateMax);

It ignores the dates regardless of the date parameter and provides a distinct total!

I was using {'>=\$(vStartDate)<=\$(vEndDate)'}>} but then hardcoded the dates to prove the process was flawed.

= count({<DirectDebtDate ={"\$(= '>=' & '2016-10-01' & '<=' & '2016-10-31')"},FiscalYear=,WeekDay=,Quarter=, Month=>}  Distinct TransactionID)

= count({<DirectDebtDate ={"\$(= '>=' & '2016-11-01' & '<=' & '2016-11-30')"},FiscalYear=,WeekDay=,Quarter=, Month=>}  Distinct TransactionID)

= count({<DirectDebtDate ={"\$(= '>=' & '2016-12-01' & '<=' & '2016-12-31')"},FiscalYear=,WeekDay=,Quarter=, Month=>}  Distinct TransactionID)

all provide the same incorrect results?

I tried also tried:

=Count({<DirectDebMonthID ={"\$(=Date(Max(MonthID),'YYYY-MM'))-*"},FiscalYear=,WeekDay=,Quarter=, Month= >}  Distinct TransactionID)  and get the same incorrect answer.

I would like 3 expressions if possible for and have variables already available:

\$(vPreviousMonth)

\$(vCurentMonth)

\$(vNextMonth)

Any help would be much appreciated

Thanks