Complex Date

[smilingjohn]

HI ALl

sum({ $<[Fiscal Year] ={$(=vFisCurrYear)},[Fiscal Month ]={$(=vFisPrevMonth)},Month_DG=>}Cost

This expression is for current year previous month ..

And my fiscal month starts from April as 1st month

When i select  June this expression will display May releated data ( Previous MOnth)

and when i select April this has to display the data for March( which is of previous Year) ..

how can i include this condition in this Expression ...

Below is the content within the field

vFisPrevMonth=Max([Fiscal Month])-1

[Fiscal Year] = 2016,2017,2018

[Fiscal Month]= 1,2,3,4,....so on

vFisCurrYear= =MAX([Fiscal Year])

Can please someone suggest me on this ?

Thanks in advance

[Anil_Babu_Samineni]

If you selecting April from Fiscal Month what you are getting in text object using this expression

=Max(FiscalYear)

OR

=Max(FiscalYear)-1

[smilingjohn]

=Max(FiscalYear)  is Giving me 2018

and Max(FiscalYear)-1 is giving me 2017

and For max(Fiscal Month) its giving 1

for max(Fiscal Month) its giving 0

[#4]

HI john,

what you want exactly give sample output?

[smilingjohn]

Hi Thirumal ..

I

one will give current month data

one more column will give the prvious month data ..

And for previous month data i have shared   the expression ..

sum({ $<[Fiscal Year] ={$(=vFisCurrYear)},[Fiscal Month ]={$(=vFisPrevMonth)},Month_DG=>}Cost

NOw the fiscal month starts from April

When i select April ..the Previous month Column should display the data for March( which will become previous years)

I wanted to include this requirement in the given expression

[sunny_talwar]

I suggest creating a new field in the script called MonthYear

LOAD Date,

     ...,

     Date(MonthStart(Date), 'MM-YYYY') as MonthYear

FROM ...;

And then this

Sum({$<[Fiscal Year], [Fiscal Month], Month_DG, MonthYear = {"$(=Date(AddMonths(Max(MonthYear), -1), 'MM-YYYY'))"}>} Cost)

[smilingjohn]

HI Sunny Thanks for the reply

Cant we do it in the existing scripture by applyinf any if condition ?

like if Current month is 1 the this expression something like that .

Becuase already there is a master calendar implemented in this ..

Please provide any suggestion

[sunny_talwar]

You can, but you are bringing in unnecessary complexities. You mentioned that you already have a mastercalendar... can you share the script it uses? because you might already have a monthyear field which we might be able to use in this case

[smilingjohn]

Hi Sunny

Below is the master calendar being used in this script ...

LET vMinDate = num(MakeDate(2016, 1));

LET vMaxDate = num(YearEnd(Today()));

SET vFiscalMonthStart = 7; //e.g. use 4 if Fiscal Start is April

LET vFiscalMonthEnd = if(vFiscalMonthStart = 1, 12, vFiscalMonthStart - 1);

Directory; //Current directory

//------------------- Temporary Calendar -------------------

TempCalendar:

LOAD

$(vMinDate)+IterNo()-1 AS Num_MIS,

Date($(vMinDate)+IterNo()-1,'MM/DD/YYYY') AS TempDate_MIS

AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

//----------------------------------------------------------

FWMasterCalendar:

LOAD

  TempDate_MIS, //04/29/2007

  TempDate_MIS AS [END_DATE_MIS], //04/29/2007

  MonthStart(TempDate_MIS) AS [Month Start Date_MIS], //04/01/2007

  MonthEnd(TempDate_MIS) AS [Month End Date_MIS], //04/30/2007

  //Year

  Year(TempDate_MIS) AS Year_MIS, //2007

  'Year' AS [Full Year_MIS], //Year

  Right(YearName(TempDate_MIS,0,$(#vFiscalMonthStart)),4) AS [Fiscal Year_MIS], //2008 //If($(#vFiscalMonthEnd) < 12 and Month(TempDate_MIS) >= $(#vFiscalMonthStart), Year(TempDate_MIS)+1, Year(TempDate_MIS)) AS FYear,

  YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)) as [Fiscal Year Name_MIS], //2007-2008

  //Semester

  'H' & If(Month(TempDate_MIS) < 7, 1, 2) AS [Semester_MIS], //H1

  'H' & If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 1,

      2) AS [Fiscal Semester_MIS], //H1

  'H' & If(Month(TempDate_MIS) < 7, 1, 2) & ' (' & Year(TempDate_MIS) & ')' AS [Semester Period_MIS], //H1 (2007)

  'H' & If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 1, 2)

      & ' (' & Mid(YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)), 3, 2) & '-' & Mid(YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)), 8, 2) & ')' AS [Fiscal Semester Period_MIS], //H1 (07-08)

  //Quarter

  'Q' & Alt(If(Month(TempDate_MIS)<4,1),if(Month(TempDate_MIS)<7,2),if(Month(TempDate_MIS)<10,3), 4) AS Quarter_MIS, //Q2

  'Q' & Alt(If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 4, 1,

        If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 2,

        If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 10, 3,

        4)))) as [Fiscal Quarter_MIS], //Q1

  QuarterName(TempDate_MIS) as [Quarter Name_MIS], //Apr-Jun 2007

  QuarterName(TempDate_MIS, 0, $(vFiscalMonthStart)) as [Fiscal Quarter Name_MIS], //Apr-Jun 2007

  'Q' & Alt(If(Month(TempDate_MIS)<4,1),if(Month(TempDate_MIS)<7,2),if(Month(TempDate_MIS)<10,3), 4) & ' (' & Mid(QuarterName(TempDate_MIS), 1, 7) & ')' as [Quarter Months_MIS], //Q2 (Apr-Jun)

  'Q' & Alt(If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 4, 1,

        If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 2,

        If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 10, 3,

        4)))) & ' (' &

    Mid(QuarterName(TempDate_MIS, 0, $(vFiscalMonthStart)), 1, 7) & ')' as [Fiscal Quarter Months_MIS], //Q1 (Apr-Jun)

  Alt(If(Month(TempDate_MIS)<4,1),if(Month(TempDate_MIS)<7,2),if(Month(TempDate_MIS)<10,3), 4) AS [Quarter No_MIS], //2

  Alt(If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 4, 1,

        If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 2,

        If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 10, 3,

        4)))) as [Fiscal Quarter No_MIS], //1

  'Q' & Alt(If(Month(TempDate_MIS)<4,1),if(Month(TempDate_MIS)<7,2),if(Month(TempDate_MIS)<10,3), 4)

  & ' ' & Year(TempDate_MIS) AS [Quarter Period_MIS], //Q2 2007

  'Q' & Alt(If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 4, 1,

        If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 2,

        If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 10, 3,

        4))))

   & ' (' & Mid(YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)), 3, 2) & '-' & Mid(YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)), 8, 2) & ')' AS [Fiscal Quarter Period_MIS], //Q1 (07-08)

  //Month

  Num(Month(TempDate_MIS)) as [Month No_MIS], //4

  If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) AS [Fiscal Month No_MIS], //1

  Month(TempDate_MIS) as Month_MIS, //Apr

  Mid(Date(MonthStart(TempDate_MIS),'YYDDMMMM'), 5) as [Month Name_MIS], //April

  Date(MonthStart(TempDate_MIS),'MMM-YY') AS [Month Period_MIS],//Apr-07 //GL Period

  //Week

  Weekday(TempDate_MIS) AS Weekday_MIS, //Sun

  WeekName(TempDate_MIS, 0, 0) as [Week Period_MIS], //2007/17

  'W' & Week(TempDate_MIS) AS [Week Name_MIS], //W17

  Week(TempDate_MIS) AS [Week No_MIS], //17

  //Week Numbers within Month (Week Starting Monday): Current Week No - MonthStart Week No, if < 0 then (Previous Week No) - Month Start Week No + 1)

  Date(MonthStart(TempDate_MIS),'MMM-YY') & ' W' &

    If( ( Week(TempDate_MIS) - Week(MonthStart(TempDate_MIS)) + 1 ) < 0,

      ( Week(WeekEnd(TempDate_MIS, -1 * (Week(TempDate_MIS)))) + (Week(TempDate_MIS)) - Week(MonthStart(TempDate_MIS)) + 1 ),

      ( Week(TempDate_MIS) - Week(MonthStart(TempDate_MIS)) + 1 ) ) as [Week No. Month_MIS], //Apr-07 W5

  'W' & Week(Weekend(TempDate_MIS,0,-1) ) as [Week Name Sun Start_MIS], //W18

  Week(Weekend(TempDate_MIS,0,-1) ) as [Week No Sun Start_MIS], //18

  Date(MonthStart(TempDate_MIS),'MMM-YY') & ' W' &

      If( ( Week(Weekend(TempDate_MIS,0,-1)) - Week(Weekend(MonthStart(TempDate_MIS),0,-1)) + 1 ) < 0,

      ( Week(WeekEnd(TempDate_MIS, -1 * (Week(Weekend(TempDate_MIS,0,-1))),-1)) + (Week(Weekend(TempDate_MIS,0,-1))) - Week(Weekend(MonthStart(TempDate_MIS),0,-1)) + 1 ),

      ( Week(Weekend(TempDate_MIS,0,-1)) - Week(Weekend(MonthStart(TempDate_MIS),0,-1)) + 1 ) ) as [Week No. Sun Start Month_MIS], //Apr-07 W5

  //Day

  DayNumberOfYear(TempDate_MIS) as [Day Number of Year_MIS], //120

  DayNumberOfYear(TempDate_MIS, 4) as [Fiscal Day Number of Year_MIS], //29

  Day(TempDate_MIS) AS [Day No_MIS], //29

  If (Day(TempDate_MIS) < 4 OR Day(TempDate_MIS) > 20, (If(Right(Day(TempDate_MIS),1)=1, day(TempDate_MIS) & 'st',(If(Right(Day(TempDate_MIS),1)=2, day(TempDate_MIS) & 'nd',(If(Right(Day(TempDate_MIS),1)=3, Day(TempDate_MIS) & 'rd', Day(TempDate_MIS) & 'th')))))), Day(TempDate_MIS) & 'th') AS [Day th_MIS], //29th

  RowNo() AS RowNo_MIS

RESIDENT TempCalendar

ORDER BY TempDate_MIS ASC;

STORE FWMasterCalendar INTO [..\data\FWMasterCalendar.qvd];

Drop Table TempCalendar ;

[smilingjohn]

Sunny As i mentioned this expresion are used in the straight table and are giving the correct result ..

Only the problem comes where they select the First month of the Fiscaly year and the prvious month will show zero value..

Therefore i want to use something based on condition when 1st month is slected it should show the prvious month ( that beacomes the 12month of the last year) proper data

I hope you got sunny what i want to say