topic Re: Chart with Date Columns in QlikView

Chart with Date Columns

Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to build a chart with our chart of accounts as the dimension, using the current months total $(Total) as the measurement, which gives me the total from the selected date. I then want to add 11 columns that will return the total amounts from the prior 11 months, so that I always have a running 12 month total for each account listed.

Re: Chart with Date Columns

rubenmarin — Fri, 20 Jan 2017 23:54:35 GMT

Hi Dave,

In some scenarios it can be precalculated on script but usually you need a solution like The As-Of Table

Here you have another document of the same subject with some table examples: Calculating rolling n-period totals, averages or other aggregations

Re: Chart with Date Columns

vinieme12 — Sat, 21 Jan 2017 15:46:55 GMT

create a YearMonth field in your data table to simplify the calculations

example: 201601,201602 and so on

and vStartYearMonth = Date(Addmonths( Date(Max(YearMonth),'YYYYMM'),-11),'YYYYMM')

You expression should then be

SUM ( {< YearMonth = {">=$(vStartYearMonth))"} >} Sales)

Just ensure your Month variables are '00' format meaning Jan is '01' and not '1'

Period Presets: Compare Periods on the fly