Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average of values in the Month to Date

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.

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

3 Replies
Not applicable
Author

Data source is attached...

Not applicable
Author

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.

Not applicable
Author

That's excellent, thanks!