topic Rolling sum Syntax help in QlikView

Rolling sum Syntax help

Thu, 26 Jan 2017 14:19:08 GMT

Hello All,

I'm looking for some sytanx help. I have a value in January that is loaded with the base number for the year. In the following months the change in that number is expressed. So in January I have 500 and in February I could have 2, with a total of 502. Than in march my value could be -3 leaving a total of 499. I want a dynamic way to show that 502 throughout the year. I know I need to create a variable for start as like 1.1. and using the year date. but I'm having trouble with the end date and how to construct the set analysis.

Thanks,

Re: Rolling sum Syntax help

sunny_talwar — Thu, 26 Jan 2017 14:22:04 GMT

You want to show 502 throughout the year? Why 502? I am not sure I understand what you are looking to do... can you may be share a sample app or sample data?

Re: Rolling sum Syntax help

Thu, 26 Jan 2017 15:22:02 GMT

No sorry, I want it live. So In febuary I Want to show 502 and in march I want to show 499.

Re: Rolling sum Syntax help

Thu, 26 Jan 2017 15:22:51 GMT

The way the data is set up is theirs a base number in January (its for # of employees in a given section) and then any changes are shown from feb-dec.

Re: Rolling sum Syntax help

sunny_talwar — Thu, 26 Jan 2017 15:26:52 GMT

Would you be able to share some sample data or sample qvw?

Re: Rolling sum Syntax help

Thu, 26 Jan 2017 15:42:52 GMT

I want a dynamic text object or way to express that total of 512 (I have a year selected here as well). So say it was March I want to show 467=(463+4) - The data updates once a month so it probably wouldn't have data for march yet.

Re: Rolling sum Syntax help

Thu, 26 Jan 2017 15:43:31 GMT

The whole table was too long and didn't show - that 512 is a total of all the months for the selected year.

Re: Rolling sum Syntax help

sunny_talwar — Thu, 26 Jan 2017 16:42:01 GMT

May be like this:

RangeSum(Before(YourExpression, 0, ColumnNo()))

Re: Rolling sum Syntax help

Thu, 26 Jan 2017 17:55:21 GMT

Thanks, that would be for a table. I'm trying to have this as a separate text object or throw into a gauge chart.

Re: Rolling sum Syntax help

sunny_talwar — Thu, 26 Jan 2017 18:03:07 GMT

Assuming your expression is Sum(Measure), try this

Sum({<DateField = {"$(='>=' & Date(YearStart(Max(DateField)), 'DateFieldFormatHere') & '<=' & Date(Max(DateField), 'DateFieldFormatHere'))"}>} Measure)

Above, DateField is a placeholder for the date field in your dashboard and use the date fields's format to replace DateFieldFormatHere

Lots of assumptions above, but I hope you can find your way around the above

Re: Rolling sum Syntax help

Fri, 27 Jan 2017 16:54:51 GMT

Thank you! A version of that worked for the most part just need to get the right variables for dates in there now so they update automatically. I used the following:

=Sum({<Date = {">=$(='1/01/2015')<=$(='12/31/2016')"}, >} Measure)

So now I need to set my variables for those two dates with the year changing because i realized the data comes in month by month so it doesn't matter if im summing the total year. so I want VStartDate to be 01/01/Current year and Venddate to be 12/31/Current year. Can you assist with creating these variables and the syntax for putting it in the sum and set analysis. I seem to get syntax issues when I add variables instead of actual dates.

Re: Rolling sum Syntax help

sunny_talwar — Fri, 27 Jan 2017 17:12:52 GMT

Try this:

Sum({<Date = {"$(='>=' & Date(YearStart(Today()), 'MM/DD/YYYY') & '<' & Date(YearStart(Today(), 1), 'MM/DD/YYYY'))"}>} Measure)