Hello! I need some advice.
I load from source a table called Invoices that looks (more or less) like this:
Location | Year | Month | Customer| Item | Sales | Etc...
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.
