topic QTD and Previous QTD in QlikView

QTD and Previous QTD

abhaysingh — Sat, 04 Jul 2015 09:48:00 GMT

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

Re: QTD and Previous QTD

marcus_sommer — Sat, 04 Jul 2015 10:44:34 GMT

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

Re: QTD and Previous QTD

swuehl — Sat, 04 Jul 2015 11:12:48 GMT

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.

Re: QTD and Previous QTD

abhaysingh — Sat, 04 Jul 2015 11:48:53 GMT

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

Re: QTD and Previous QTD

PrashantSangle — Sat, 04 Jul 2015 11:56:39 GMT

Hi,

use Addmonths(max(DATE),-3)

Regards

Re: QTD and Previous QTD

abhaysingh — Sat, 04 Jul 2015 12:06:29 GMT

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.

Re: QTD and Previous QTD

swuehl — Sat, 04 Jul 2015 12:13:55 GMT

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])

Re: QTD and Previous QTD

abhaysingh — Sat, 04 Jul 2015 12:16:51 GMT

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

Re: QTD and Previous QTD

swuehl — Sat, 04 Jul 2015 12:26:19 GMT

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.