Rooling Totals

[abc_18]

Hi,

I want to calculate one column(FY'15 Actual) based on this condition.

if(Division='PUMA',sum(H2:S2),sum(E2:P2))  as FY'15 Actual.

Please find details in attachments.

[abc_18]

I am using this calendar script in my application.

Set vFM = 10 ;    // First month of fiscal year(Fiscal year starts from OCT-SEPT)

Set vFD = 6;     // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

MasterCalendar:

Load distinct

   Dual(fYear-1 &'/'& fYear, fYear) as FYear,   // Dual fiscal year

   Dual(Month, fMonth)                as FMonth,  // Dual fiscal month

   Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

   Ceil((Date-StartOfFWeekOne+1)/7) as FWeekNo,

   *;

Load Year + If(Month>=$(vFM), 1, 0)   as fYear,   // Numeric fiscal year

   Mod(Month-$(vFM), 12)+1            as fMonth,  // Numeric fiscal month

   Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,

   WeekStart(FYearStart,0,$(vFD))     as StartOfFWeekOne,

   *;

Load

   Date,

   YearStart(Date,0,$(vFM)) as FYearStart,

   Year(Date)               as Year,

   Month(Date)              as Month,

   Date(Monthstart(Date), 'MMM-YYYY') as MonthYear,

   Week(Date)               as ISOWeekNo,

   Dual(WeekDay(Date),Mod(WeekDay(Date-$(vFD)),7)+1) as WeekDay,

   Day(Date)                as Day,

   Date(Date, 'MM/DD')      as DATEMMDD

Resident Result;   

so if Division is 'PUMA', then value need to calculate based on year ie' Jan-15 to Dec-15 else for other division

calculation is based on fiscal year(Oct-14 to Sep-15).

only for PUMA division we are following this year format(Jan-15 to Dec-15).

Please suggest,

[sunny_talwar]

Are you loading the data using CrossTable Load?

[abc_18]

Hi,

yes, I am loading data using cross table load.

This is the script.

Temp:

CrossTable(Date, Value, 15)

LOAD *

   FROM

(ooxml, embedded labels, table is Working);

Result:

LOAD *, Date(num#(Date),'MMM-YY') as Date2,

text([Cost Code]) AS Code

Resident Temp;

drop table Temp;

drop field Date;

rename Field Date2 to Date;

[Anonymous]

may be helpful for you?

Set Analysis for Rolling Periods

Set Analysis for certain Point in Time

[abc_18]

Hi Balraj,

Thanks for your reply, that's a useful post.

but my requirement is like if I select year 2015 and division puma then value should come from Jan 15-Dec-15

else , from Fiscal year ie; Oct-14-Sept-15 .

how to implement that logic in Expression.