Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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

Average of values in the Month to Date

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

Average of values in the Month to Date

Data source is attached...

Not applicable

Average of values in the Month to Date

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

Not applicable

Average of values in the Month to Date

That's excellent, thanks!