Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to aggregate/consolidate date fields?

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

10-15-2014 8-23-39 AM.png

5 Replies
anbu1984
Honored Contributor III

Re: How to aggregate/consolidate date fields?

Can you post sample qvw

gabriel_kirst
Contributor III

Re: How to aggregate/consolidate date fields?

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.

MVP
MVP

Re: How to aggregate/consolidate date fields?

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

Dimension: Date

Exp: Sum(AmountField)

Not applicable

Re: How to aggregate/consolidate date fields?

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 #)

gabriel_kirst
Contributor III

Re: How to aggregate/consolidate date fields?

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

Community Browser