topic Re: Quater End in QlikView

Quater End

Fri, 11 Jan 2013 06:40:50 GMT

I want sales wrt finicial year only for which quater has ended.Example

For year 2012-13 my three quater is completed i.e q1,q2,q3 so my sales should come but for q4 only two mnths is compeletd so no data should come for q4 of 2012-13.

Please help me out...

Re: Quater End

Fri, 11 Jan 2013 07:21:57 GMT

I propose to move the relevant data to a new table by using QuarterStart and QuarterEnd in a where-clause:

NewTable:

LOAD

Date as MaxLastQ_Date,
Sale as MaxLastQ_Sale
FROM quarter_input.xls
(biff, embedded labels, table is Sheet1$)
WHERE QuarterEnd(Date) < QuarterStart(Today());

Regards,

sebablum

Re: Quater End

Vegar — Fri, 11 Jan 2013 07:23:15 GMT

I'll would look for which quarter tomorrows date fall under and then exclude it.

Example1: My last transaction data is from 2012-12-31 (q4 2012), the day after is 2013-01-01 (q1 2013). I will only include Quarters < q1 2013

Example2: My last transaction data is from 2012-12-15 (q4 2012), the day after is 2013-12-16 (q4 2012). I will only include Quarters < q4 2012.

Re: Quater End

Fri, 11 Jan 2013 08:12:03 GMT

@sebablum

i want this condition to be applied on the front end where i want to see data on the basis of finicial year and quarter

Re: Quater End

Vegar — Fri, 11 Jan 2013 08:30:43 GMT

Then I would have implemented a calendar with a Quarter index of some kind.

sum({<_QuarterSerial={"<$(=max(_QuarterSerial))"}>}Sale)

Re: Quater End

Fri, 11 Jan 2013 08:51:47 GMT

@Vegar Arntsen, Lie

Can u tell me how u have calculated _QuarterSerial. And If possible please provide me the .qvs file that u have included in an application

Re: Quater End

Fri, 11 Jan 2013 09:29:13 GMT

try this code

LOAD date,

Sale

FROM

D:\test35.xls

(biff, embedded labels, table is Sheet1$);

LOAD *,

Day(date) as day,

Month(date) as month,

Year(date) as year,

yearname(date,0,4) as FinancialYear;

LOAD

date(Date#(date,'DD.MM.YYYY')) as date,

Sale

FROM

D:\test35.xls

(biff, embedded labels, table is Sheet1$); .

Re: Quater End

Fri, 11 Jan 2013 09:37:16 GMT

@Vegar Arntsen, Lie

Ur code runs perfectly but my finicial begin from april 2011 to mar 2012. thats why i need ur qvs file so that i changed year format

Re: Quater End

Fri, 11 Jan 2013 10:30:01 GMT

@Vegar Arntsen, Lie

Please provide me ur Qvs file..

Re: Quater End

Vegar — Fri, 11 Jan 2013 15:58:31 GMT

It is the QlikView Components code library.

You find it at http://qlikviewcomponents.org