Accumulating values by dates

[#1]

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

[Clever_Anjos]

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

[#3]

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

[#4]

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

[#5]

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.

[Clever_Anjos]

First, at script level create some fields to help us

1)

   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

[Clever_Anjos]

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