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

    Summing up  for 3 different periods

    m ellis

      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