QTD and Previous QTD

[abhaysingh]

Hi all,

Can any one help me to calculate QTD and Previous QTD?

I am following Fiscal year, Apr to March,

Calculating Quarterstart and Quarter end  as below

Date(QuarterStart([DATE]))					AS [Quarter Start],
Date(QuarterEnd([DATE]))						AS [Quarter End],

=sum({< [Quarter Start] = {'>=$(=QuarterStart(max([Quarter Start]))) <=$(=(max([Quarter End])))'}>}[Credit In Company Currency])

-sum({< [Quarter Start] = {'>=$(=QuarterStart(max([Quarter Start]))) <=$(=(max([Quarter End])))'}>}[Debit In Company Currency])

please let me know if required any thing else from my side..

Thanks

Abhay

[marcus_sommer]

Have a look on these posting How to use - Master-Calendar and Date-Values. The last three links led to excellent posting about the advanced comparing from periods. And I think the other topics there will be helpful for you, too.

- Marcus

[swuehl]

Maybe something like for QTD

=sum({< [DATE] = {">=$(=QuarterStart(Today()))<=$(=Today())"}>} [Credit In Company Currency])

-sum({< [DATE] = {">=$(=QuarterStart(Today()))<=$(=Today())"}>}[Debit In Company Currency])

You may need to clear selections in all calendar fields, the user may select in (assuming the stated four fields as example only):

=sum({< [DATE] = {">=$(=QuarterStart(Today()))<=$(=Today())"}, [Quarter Start], [Quarter End], Month, Year >} [Credit In Company Currency])

-sum({< [DATE] = {">=$(=QuarterStart(Today()))<=$(=Today())"}, [Quarter Start], [Quarter End], Month, Year >}[Debit In Company Currency])

Similar for previous QTD:

=sum({< [DATE] = {">=$(=QuarterStart(Today(),-1))<=$(=Addmonths(Today(),-3))"}>} [Credit In Company Currency])

-sum({< [DATE] = {">=$(=QuarterStart(Today(),-1))<=$(=Addmonths(Today(),-3))"}>}[Debit In Company Currency])

Again, you'll need to clear selections in other calendar fields.

[abhaysingh]

Hi Swuehl,

Thanks For revert but getting one issue, Addmonths(Today(),-3)) , always will give me current date minus three month .

but i want it should give me 01-04-2015 to 30-06-2015.. could you pls help me in this.

thanks

abhay

[PrashantSangle]

Hi,

use Addmonths(max(DATE),-3)

Regards

[abhaysingh]

Hi all,

See the image i am able to get the Delta period of in Between Dates but not getting value.. can any help me in this..

thanks.

[swuehl]

Do you have calendar dates in that range?

For previous quarter (but that's not called previous QTD), you can use:

Similar for previous Quarter:

=sum({< [DATE] = {">=$(=QuarterStart(Today(),-1))<=$(=QuarterEnd(Today(),-1))"}>} [Credit In Company Currency])

-sum({< [DATE] = {">=$(=QuarterStart(Today(),-1))<=$(=QuarterEnd(Today(),-1))"}>}[Debit In Company Currency])

[abhaysingh]

See My Calender

LET vNoOfRows = Null();

LET vFileSize = Null();

LET vFileTime = Null();

LET vFileTracker_TableName = Null();

LET vTableName = Null();

LET vFileTrackerPath = Null();

LET FiscalMonthStart = Null();

LET DatabaseStartYear = Null();

LET FiscalMonthAdjust = Null();

LET DAYS = Null();

LET vStartTime=Now();

//Variable Declarations

LET vTableName = 'Stage2_Common_Calendar';

//LET vFileTracker_TableName = 'FileTracker_$(vTableName)';

//LET vFileTrackerPath = '$(FileTracker_Stage2)';

LET FiscalMonthStart = '4';

LET DatabaseStartYear = year(yearend(today()-1,0,$(FiscalMonthStart)))-1;

LET FiscalMonthAdjust = $(FiscalMonthStart)-1;

LET DAYS = ceil(YearEnd(Today())-MakeDate($(DatabaseStartYear)));

Calendar1:

LOAD date(Makedate($(DatabaseStartYear))+recno()-1) AS [DATE],

     year(date(makedate($(DatabaseStartYear))+recno()-1)) AS [YEAR],

     month(date(makedate($(DatabaseStartYear))+recno()-1)) AS [MONTH]

AUTOGENERATE ($(DAYS));

Calendar2:

LOAD date([DATE],'DD-MM-YYYY') AS [Calendar Date],

     date([DATE],'DD-MM-YYYY') AS [link_Date],

     day([DATE]) AS Days,

     week([DATE]) AS Weeks,

     WeekDay([DATE]) AS WeekDays,

     Date(Floor(WeekStart([DATE])),'DD-MMM') AS [Week Start],

     Date(Floor(WeekEnd([DATE])),'DD-MMM') AS [Week End],

     [YEAR] AS [Calendar Year],

     [MONTH] AS [Calendar Month],

     if (month([DATE])<=$(FiscalMonthAdjust),month([DATE])+(12-$(FiscalMonthAdjust)), month([DATE])-$(FiscalMonthAdjust)) AS [Fiscal Month Num],

     NUM(month([DATE])) AS [Calendar Month Num],

     dual(if(MONTH>=1,'Q')& ceil(if(MONTH<=$(FiscalMonthAdjust),MONTH+12-$(FiscalMonthAdjust),MONTH-$(FiscalMonthAdjust))/3),

     ceil(if(MONTH<=$(FiscalMonthAdjust),MONTH+12-$(FiscalMonthAdjust),MONTH-$(FiscalMonthAdjust))/3)) AS [Fiscal Quarter],

     year(yearname([DATE],0,$(FiscalMonthStart))) AS [Fiscal Year],

     yearname(DATE,0,$(FiscalMonthStart) ) as FYTEst,

     left(yearname([DATE],0,$(FiscalMonthStart)),5)&right(yearname([DATE],0,$(FiscalMonthStart)),2) AS [FY Year Name],

     Date(MonthStart([DATE]),'DD-MM-YYYY') AS [Month Start],

     Date(MonthEnd([DATE]),'DD-MM-YYYY') AS [Month End],

     Date(QuarterStart([DATE])) AS [Quarter Start],

     Date(QuarterEnd([DATE])) AS [Quarter End],   

     Year(YearName([DATE],0,$(FiscalMonthStart)))*100+(if (month([DATE])<=$(FiscalMonthAdjust),month([DATE])+(12-$(FiscalMonthAdjust)),month([DATE])-$(FiscalMonthAdjust))) AS [Fiscal YYYYMM]

RESIDENT Calendar1;

DROP TABLE Calendar1;

Calendar_Final:

LOAD

  *,

  dual([Calendar Month]&chr(39)&right([Calendar Year],2),[Fiscal YYYYMM]) AS [Calendar Month Year],

  dual([Calendar Month],[Fiscal Month Num]) AS [Fiscal Month]

RESIDENT Calendar2;

DROP TABLE Calendar2;

thanks

[swuehl]

What do you want me to do?

I intended to ask if there are facts within the time range you are requesting, because you are essentially summing up these credt and debit values that need to have records related to these time range.

I suggest you upload a small sample QVW, this would make it much easier to help you.