topic Re: Accumulating values by dates in QlikView

Accumulating values by dates

Fri, 20 Sep 2013 19:16:49 GMT

Hello,

More detail:

I have a table with some ammounts and a table with dates (and other values not important for this).

They connect thru a primarykey of ID.

I want to be able to accumulate the amounts of the first table by month/year of the second table.

The table isnt ordered by dates,

Any ideas?

thanks

Re: Accumulating values by dates

Clever_Anjos — Fri, 20 Sep 2013 20:49:49 GMT

You want to do it at script level or graph object level?

Re: Accumulating values by dates

Mon, 23 Sep 2013 14:45:30 GMT

I would love to do it at graph object level, but the problem is that the expressions used there fluctuate depending on the selection.

Re: Accumulating values by dates

Mon, 23 Sep 2013 15:10:24 GMT

You might have to add some fields to your table with dates, like:

Year(TempDate)

as MYear,

Month(TempDate)

as MMonth,

Num(Month(TempDate))

as MMonthnr,

Day(TempDate)

as MDay,

Weekday(TempDate)

as MWeekday,

'Q' & Ceil(Month(TempDate)/3)

as MQuarter,

Date(MonthStart(TempDate),'MM-YYYY')

as MMonthYear

This has to be done within the script.

Now if your tables are correctly linked it shouldn't be a problem choosing MMonth, Myear or MMonthYear as a dimension for a chart and adding SUM(Amounts) as an expression

Re: Accumulating values by dates

Mon, 23 Sep 2013 15:31:37 GMT

But in script level would work also.

I originaly thought i could create an extra column that would add the values, but it has to be order by date and in my table it isnt.

Re: Accumulating values by dates

Clever_Anjos — Mon, 23 Sep 2013 16:21:49 GMT

First, at script level create some fields to help us

YEAR(yourdatefield) as Year,

Month(yourdatefield) as Month,

Use those fields as dimentions of a Straight Table, and use Full Accumulation checkbox to instruct QlikView to rollup your sums

Re: Accumulating values by dates

Clever_Anjos — Mon, 23 Sep 2013 20:53:37 GMT

If you want it ordered by date, add your date as dimention and use "Sort" tab to order it