Sales Last Year

[#1]

Hi All,

I am using the following to get sales last year and year before last year but its hsowing blank values in the app. Any idea why?

ReportingDate is a column in the table.

Sales:LOAD *,If(Year(ReportingDate)=Year($(vTodaysDate))-2,[SalesDlrs]) as SalesDlrs2YrsAgo,If(Year(ReportingDate)=Year($(vTodaysDate))-1,[SalesDlrs]) as SalesDlrsYrsAgo;SQL
SELECT * FROM SalesData];

Thanks in advance!

[swuehl]

Hi peaceout,

you might want to check on record level, if your script functions as expected.

for example go to table View (CTRL-T) and look at the table record samples via context menu.

Or use a table box. You should see your entries.

Ensure that ReportingDate and vTodayDate are set with values of date type / numericals.

Maybe your expression in the app to show the values is not correct, what do you use?

Regards,

Stefan

[#3]

Thanks for your relpy, I thought vTodayDate is built in variable, no?

Also, I looked at the values in the table view and there are no values(-) for those columns. Thoughts?

[swuehl]

Hi peaceout,

I believe vTodayDate is no built in variable, you have to define it in your script (before using it in LOAD).

Like

Let vTodayDate = today();

You could see al defined variables in the GUI,  CTRL-ALT-V (Settings - Variable Overview).

Regards,

Stefan

[gandalfgray]

Hi

And since you seems to only need the year of today why not create two variables:

Let vYear1 = Year(today()) - 1;

Let vYear2 = Year(today()) - 2;

/gg

[#6]

vTodaysDate is defined as 39660 in my document. What does 39660 mean? Is that correct?

[gandalfgray · Accepted solution]

That's the number of days since 1899-12-30.

It's an internal way of defining dates.

put:

=date(39660)

into a textbox and it will display the date 2008-07-31 (formatted in  your default date format).

Todays date (2011-09-06) expressed as a number is 40792.

/gg

[swuehl]

That is the numerical representation of a date, 2008-07-31 in this case.

Should be ok as input to date functions, if you want it to be displayed as date, use

Date($(vTodayDate))

[swuehl]

peaceout,

today() should give you today's date at reload, do you have an idea why you got 2008-07-31 back?

Anyway, I think we need to return to the beginning:

1) check what your script results in by checking the resulting table (use a table box or the table view, see above, or even list boxes)

check that there are at least some entries for SalesDlrsYrsAgo and SalesDlrs2YrsAgo. I recommend that you add maybe

Year(today()) as YearToday,

or

Year($(vTodaysDate)) as YearToday,

and

Year(ReportingDate) as YearReporting,

to your script. You need to ensure that ReportingDate is recognized by QV as Date (and doing a Year(ReportingDate) should check this, if you don't get the correct year back, the parsing has failed.

The internal representation of a date is numerical, as you know by now, so putting a

num(ReportingDate) as NumReporting,

in the script or a =num(ReportDate) as expression in a listbox should return a positive number up to 40792.

2) If all this looks ok, we need to check the expression in the GUI: Where have you set the expression and in which context (dimensions)?

No worries, I don't think we are far from a solution.

Regards,

Stefan