Balance Sheet - Date problem

[#1]

Good day

I have build a Qlikview report that shows me a Balance Sheet.

I have a field called REFDATE which is the date when a transaction took place. (Journal date)

As you all know a Balance Sheet cant be selected for a date range, but only on a specific day. (ex 30 June 2010)

The problem I am having is when I want to see the balance sheet on any specific day in the past, (lets say 30 April 2010)

In order for me to see that I have to go select REFDATE from the start of all transactions up until 30 April 2010.

Is there a way that I can only select 30 April 2010 and it would automatically select all the dates prior to 30 April 2010.

Hope someone can help me.

Thanks

Is there a way in Qlikview that you can only select that specific day and it will automatically select the starting date up until that date.

[#2]

Try the following code while loading the script

Monthend

(invoive_date) as Month_End,

I usually create a seperate table for all date references for all dates e.g.

Date:

load

today()-recno()+1 as Date

autogenerate(today()-'2007-03-31');

DateParts:

load

date(Date) as PROCDATE,

Year(Date) as Year,

yearname ( Date, 0, 4 ) as FinancialYear,

date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,

Week(Date) &'-'& Month(Date) as Date_WeekMonth,

Month(Date) as FiscalRollMth,

Month(Date) &'-'&right(Year(Date),2) as RollShortMth,

Month(Date) as Month,

Monthend(Date) as Month_End,

Day(Date) as Day,

Week(Date) as Week,

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

Weekday(Date) as WeekDay

resident Date;

drop table Date;

[#3]

THIS IS HOW MY SCRIPT LOOKS LIKE AT THE MOMENT. HOW COULD I IMPLIMENT IT INTO THIS?

"ACCOUNTS":

SELECT

TransId,

Account,

Credit,

Debit,

ProfitCode,

RefDate,

ShortName

FROM

"SBO_O_SA1".dbo.JDT1;

"CALENDAR":

LOAD

RefDate,

Year

(RefDate) as Year,

Month

(RefDate) as Month,

Date

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

'Q'

& Ceil(Month(RefDate)/3) as Quarter,

Dual

(Year(RefDate) & '-Q' & Ceil(Month(RefDate)/3), Year(RefDate) & Ceil(Month(RefDate)/3)) as YearQtr,

Week

(RefDate) as Week,

Day

(RefDate) as Day

RESIDENT

ACCOUNTS;

[#4]

Monthend(REFDATE) as Month_End,

Month_End is now the new field

[#5]

Thank you, but this is almost correct. The only problem now is when I select a Month_End date it only gives me the data for that month and that year. You see my Balance sheet as of today are made out of data from May 2004 up to today. I need to somehow select a month end date at it will automatically select all prior years & days up until that date.

[#6 · Accepted solution]

left(Monthend(REFDATE),5) as Month_End,