topic Re: Aggregation Conundrum in QlikView

Aggregation Conundrum

Fri, 06 Jun 2014 12:26:42 GMT

Hello! I need some advice.

I load from source a table called Invoices that looks (more or less) like this:

I have a pivot table in QlikView with dimensions of Location and Month. The expressions are "Sales for Selected Year" and "Sales for Previous Year"; they look like this:
Sum({<Year={$(=Max(Year))} >} [Sales])
and
Sum({<Year={$(=Max(Year - 1))} >} [Sales]).

Year is a single select listbox.

As such, it works fine. But here's the caveat:

The only sales that should contribute to the sum are those where there are nonzero sales during both time periods. In SQL terms, suppose we're comparing years 2013 and 2012.

Select Location, Year, Month, Customer, Item, SUM(Sales)
from Invoices
where Year in (2013, 2012)
group by Location, Year, Month, Customer, Item

BOTH of the lines below should contribute to the total.

NYC | 2013 | May | ACME | ITEM1 | $500
NYC | 2012 | May | ACME | ITEM1 | $400

However NEITHER of the lines below should contribute to the total (because there were no sales of ITEM5 to ACME from location NYC during May 2012).

NYC | 2013 | May | ACME | ITEM5 | $200
NYC | 2012 | May | ACME | ITEM5 | $0

So there's this arbitrary sort of intermediate grouping and filtering that needs to happen. I thought of creating a separate aggregate table from the main Invoices table, and working off of that, but it seems cumbersome. Any thoughts? Thank you.

Re: Aggregation Conundrum

Fri, 06 Jun 2014 12:54:59 GMT

Hi Brian,

can you set a flag = 1 when there are sales for a month or 0 if not

and

Sum({<Year={$(=Max(Year))} >} [Sales] * Flag)

best regards

Chris

Re: Aggregation Conundrum

Fri, 06 Jun 2014 13:23:50 GMT

Thanks Chris. Problem with that is it still has to be dynamic. Suppose you have this.

NYC | 2014 | May | ACME | ITEM5 | $300

NYC | 2013 | May | ACME | ITEM5 | $200

NYC | 2012 | May | ACME | ITEM5 | $0

May 2013 sales would appear with a selection of year 2014 (and previous year 2013) but would NOT appear with a selection of 2013 (and previous year 2012).

Re: Aggregation Conundrum

Fri, 06 Jun 2014 13:38:13 GMT

Yes Brian

Tricky, you can then set 2 flags for yera and previous year

if you set a flag comparing year by month to month lik

for may 2014 / may 2013 currentflag = 1, previousflag = 1

for may 2013 / may 2012 currentflag=1 , previousflag = 0

for may 2012 / may 2011 currentflag =0, previousflag = ?

Chris

Re: Aggregation Conundrum

Fri, 06 Jun 2014 15:20:00 GMT

Okay, that's a thought. How would I go about adding this, do you think? I think it would be a change to my script after I load the Invoices table.

Re: Aggregation Conundrum

Fri, 06 Jun 2014 19:25:32 GMT

I've thought about it some more, and believe this might be the way to go -- on each line, add a flag to indicate a sale (by location/year/month/customer/item) during the previous year, and a second flag to indicate a sale during the following year.

As my QlikView scripting skills are still elementary, I'd be curious to hear how one might accomplish this. I believe I would create a second table that does the required aggregation and adds the flags, rather than trying to add flags to the existing Invoices table. I don't want to create redundancy or do something that works against QlikView, if that makes sense. I'd like to keep it simple.