topic Re: How to aggregate/consolidate date fields? in QlikView

How to aggregate/consolidate date fields?

Wed, 15 Oct 2014 12:35:28 GMT

I created a table that has the date fields below. Each date has data (numbers) in other columns associated with it.

I want to consolidate the date fields and aggregate the data associated with the same month-year. The result should be only one instance of each date (one 'Dec-2005', one 'Jan-2006') with the correlated data summed up. How can I do that?

Thank you,

Naveed

Re: How to aggregate/consolidate date fields?

anbu1984 — Wed, 15 Oct 2014 12:42:21 GMT

Can you post sample qvw

Re: How to aggregate/consolidate date fields?

Anonymous — Wed, 15 Oct 2014 12:42:22 GMT

You can try something like this on your load:

LOAD

Date,

sum(Value1) as Value1,

sum(Value2) as Value2

From YourDatasource

This you aggregate your values, grouping by your Month-Year Date.

Re: How to aggregate/consolidate date fields?

tresB — Wed, 15 Oct 2014 12:56:25 GMT

Let the data be loaded in qv as it is. Then in the front end take a straight table:

Dimension: Date

Exp: Sum(AmountField)

Re: How to aggregate/consolidate date fields?

Wed, 15 Oct 2014 13:36:04 GMT

Since it contains sensitive data, I cannot. Sorry.

It's a pretty simple load script though. The associated data points (Accepted #, Rejected #) are loaded from a different table than the date fields, but they are joined on a common key.

What I'm looking for is

Dec-2005 - Sum(December Accepted #) - Sum(December Rejected #)

Jan-2006 - Sum(January Accepted #) - Sum(January Rejected #)

Feb-2006 - Sum(February Accepted #) - Sum(February Rejected #)

Re: How to aggregate/consolidate date fields?

Anonymous — Wed, 15 Oct 2014 13:38:09 GMT

Just use it on your load:

LOAD

Date,

sum([December Accepted #]) as [December Accepted #],

sum([December Rejected #]) as [December Rejected #]

From YourDatasource

Group by Date;

Regards,

Gabriel