topic Day comparisons for a month in QlikView

Day comparisons for a month

Thu, 01 Oct 2009 21:29:29 GMT

Hi all,

To fully understand my problem please look at the following sample data

Calendar table:

DateKey Comparable Date

2008-01-21 2007-01-20

2008-01-22 2007-01-21

etc.

Sales table

Date Key Sales

2007-01-20 100

2007-01-21 200

....

2008-01-21 50

2008-01-22 70

What I want to be able to do is get a straight table when i select a year and month that looks like the following:

DateKey CurrentSales PreviousSales

2008-01-21 50 100

2008-01-22 70 200

Not sure what kind of formula or set analysis to use for this. Any suggestions would be much appreciated thanks.

Day comparisons for a month

Thu, 01 Oct 2009 21:33:40 GMT

John Witherspoon seems to have solved this eternal issue, give this a read:

http://community.qlik.com/forums/t/20915.aspx

Day comparisons for a month

johnw — Fri, 02 Oct 2009 00:28:39 GMT

New and simpler version of my solution just posted in the referenced thread. As far as your specific data, you would rearrange your tables like this:

DateKey Date Type Record ID
2008-01-21 Previous 1
2008-01-22 Previous 2
2008-01-21 Current 3
2008-01-22 Current 4

Record ID Sales
1 100
2 200
3 50
4 70

Then you just create a pivot table with DateKey and DateType as dimensions. Use sum(Sales) as the expression. Override the label for DateType to call it Sales. You should end up with this:

DateKey Sales Current Previous
2008-01-21 50 100
2008-01-22 70 200

Note that each record ID will be associated with TWO DateKeys, which isn't obvious from your specific data. Which ID you use (and thus which date you're looking at) depends on the Date Type. By the same token, each DateKey is associated with TWO days of data, so you may need to use set analysis to narrow down to one Date Type when you don't want to see both.