Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Day comparisons for a month

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.

2 Replies
Not applicable
Author

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

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

johnw
Champion III
Champion III

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.