Dynamic Ytd from selection

[#1]

Hi,

I'm currently trying create a YTD Sales values for a selected date
I've done YTD with a calendar script in a previous app with: inyeartodate(Date, '$(vtoday)', 0) * -1 AS YTD

Our customer wants to have the ability to select day, month and year and based on the selected date they want to see YTD values in a chart.

I'm writing the selection to the variable v_dateselection.

I'm not sure what's the best way to do this. Can someone help me? Thanks.

[#2]

I'd suggest using a formula in the chart like:

sum( if ( inyeartodate( Date, $(v_dateselection), 0 ), Sales, 0 )

Hope this helps!

[#3]

Hi,

I don't get it working. I guess I'm having problems with basedate?

Regards.

[#4]

Have a look at the 'Whats new in QV 9' application bundled with v9. I think you'll find the dashboard table does just that without anything in the load script??

cheers,

/mark

[#5]

First of all, I think you need a separate calendar for the reference date selection. In your application, you put a selection on DateField, and consequently only one item is selected. Thus you get only the sales figure for the date selected (if you select 1st of the month in your example), or none at all (for any other dates).

Second, I believe your variable is not interpreted as a date by QV. Maybe it would help to keep the year, month, and day values separately, and use MakeDate(). Or use a calendar box in the first place? I will look into it.

Third, I found out that the if clause produces null values, I don't know why. If I write

if ( inyeartodate( Datefield, MakeDate( 2009, 4, 1 ), 0 ) <> 0, Sales, 0 )

the result is correct. Still have to work out how to sum it up.

[#6]

Hi Mark,

I don't want to do anything in the load script.
I just want to select a certain date from my calender and get all values to that date.

e.g. I select 13.04.09 I'd like to sum up all values from 01.01.09 to 13.04.09. I can't see where What's new QV9 application is doing that or did I get something wrong?

Regards.

[#7]

Have a look at the 3rd expression colum in the 'Region Sales Scorecard'.

You'll see that the expression sums up the sales data for a year up to the selected year...

Sum({$<Year={$(=Max(Year)-1)}>} Sales / $(vCurrency))

It's Set Analysis... more info in the Help screens.

Cheers,

/mark

[#8]

Have a look at the attached example. I believe it does what you want.

[johnw]

I recommend set analysis. Separate calendars (date islands) are sometimes useful, but set analysis should be faster for this case. It's also a good idea to start practicing up with set analysis expressions since they are the solution to so very many problems.

Here's your example file with a couple of different set analysis solutions applied. No need for a separate calendar, and no need for a variable. The first and simple expression works fine for your example. The "1" tells it to ignore ALL selections, and then we tell it the date range we care about. the second and more complicated expression is probably more practical for a real world application where you might want to select some fields, such as a customer and region of a country, and still see the year to date information for those selections. In the second expression, QlikView will use all selections EXCEPT for a list of fields, which lists every field from your calendar.

Just as an additional note, I used set analysis to show your full original data set even though we've selected a date. That made it easier for me to see that I had the right results.

[stuartbeet]

Hi Jon

Your method works fine for current year YTD. How do you get the previous year's data for the same period. i.e. current YTD is 1/1/2011 to 31/7/2011 how do I get 1/1/2010 to 31/7/2010 (previous years months will always be full months (obviously). I've used -1 on the yearstart but can't find a way to get the end date of the current month from last year.

Can you help?

Much appreciated