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]:
LOAD *,
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;
LOAD
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
