Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all. I have some numeric data which is recorded on a daily basis. My users have the option to choose a particular day from a listbox, and I want to show them the actuals for the selected day, plus the average value for the selected month.
I'm really struggling to get the average for the month to date though. I'm pretty sure it needs some set analysis, but I just can't get it to work.
I have attached a qvw with an example and a further description of the problem, and I will also attach the example data for reference.
Any help would be appreciated!
Thanks
Thom.
What happens if multiple days in multiple months are selected?
I don't think this is possible with straight Set Analysis. What I did was add a field to the data called Month. This is simply the month portion of the date. Then I set up a macro that would set a variable (vDateMonth) to the value of the month of the selected date. Then I could use Set Analysis and a dollar sign expansion to filter for month equal to the variable. A {1} is needed here to exclude the date selection.
avg
({1<Month = {$(#vDateMonth)}>} Actual)
I am having a little issue with the VB Script function Month() and your data set. For some reason the format is throwing it off. The funny thing is, Month() seems to work correctly for your April dates, but pulls the Day instead for the March dates. I figure it has to do with the fact that you are using DD/MM/YYY and I have MM/DD/YYYY as my default.
Data source is attached...
What happens if multiple days in multiple months are selected?
I don't think this is possible with straight Set Analysis. What I did was add a field to the data called Month. This is simply the month portion of the date. Then I set up a macro that would set a variable (vDateMonth) to the value of the month of the selected date. Then I could use Set Analysis and a dollar sign expansion to filter for month equal to the variable. A {1} is needed here to exclude the date selection.
avg
({1<Month = {$(#vDateMonth)}>} Actual)
I am having a little issue with the VB Script function Month() and your data set. For some reason the format is throwing it off. The funny thing is, Month() seems to work correctly for your April dates, but pulls the Day instead for the March dates. I figure it has to do with the fact that you are using DD/MM/YYY and I have MM/DD/YYYY as my default.
That's excellent, thanks!