Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The question of aggregating multiple dates into a common date dimension like Month comes up frequently on this forum. I've posted a QVW tutorial on my website that shows how I typicaly handle the issue.
http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions
The QVW is not Personal Edition enabled.
-Rob
Message was edited by: Rob Wunderlich Link updated
Pls refer the datamodel, both the date cols. are in database table, have tried to club both in seperate table named as Duedate
You need to solve this by creating a link between the common date and the FILE_NO, so that a FILE_NO can have several dates. I would make a bridge table like in the following picture
where each line corresponds to either an Action date, or a Due date, or some other date. Then it is just a matter of linking this to the rest of the data through FILE_NO:
I am not sure that your current COMMON_INFO table does the above.
HIC
Thanks Henric for the help, will work on it i have used the traditional way of common calender, will let u know the results in short time,
also need a small favour,
hw to manage the Tilldate concept, whn user selects Duedate, he shld get the data in the chart till that date selected.
Thanks again
Set analysis. E.g.
Sum({$<OrderDate={"<=$(=Max(Duedate))"}>} Sales)
HIC
Hi Henric,
I can understand the link table consept, and theriticalley it should work. But if you look at this this model, why the month field in Table 3 was not connecting to the rest of the data in Table 1 and Table 2.
Thanks for your help.
Because your data isn't matching. If I select all values in Key1, then all values in Key2 and Key3 are excluded. And the same is true whichever key I use.
HIC
so if i understand, the key data need to be match to link the data across the objects. Not the data element like the one on this picture. If that is correct, what would be the solution on how to connect this data.
Hi,
Note - I've attached my QVW of the problem
I am trying to use the common calendar example linked here:
http://community.qlik.com/thread/66717
My problem is that the appointments are not adding to the table.
The total number of appointments should be 236779
The actual figures should look like this:
Month | Appointments |
Total | 236779 |
Dec | 13519 |
Aug | 13698 |
Sep | 14630 |
Feb | 16087 |
Nov | 17336 |
Mar | 17494 |
Jan | 18179 |
Oct | 18240 |
Jul | 18791 |
Jun | 26561 |
Apr | 29237 |
May | 33007 |
When I change the date to date(ActualStartDate) i get the following:
Month | sum( {<DateType={Order}>} OrderCounter) | sum( {<DateType={Shipment}>} ShipmentCounter) | sum( {<DateType={Order}>} Quantity) | Appointments | Travel Time |
Total | 6 | 11 | 621 | 236779 | 1959:03:00 |
Jan | 2 | 1 | 203 | 0 | 0:00:00 |
Feb | 1 | 3 | 103 | 0 | 0:00:00 |
Mar | 3 | 0 | 315 | 0 | 0:00:00 |
Apr | 0 | 1 | 0 | 0 | 0:00:00 |
Jun | 0 | 1 | 0 | 0 | 0:00:00 |
Jul | 0 | 0 | 0 | 0 | 1959:03:00 |
Aug | 0 | 1 | 0 | 0 | 0:00:00 |
Sep | 0 | 1 | 0 | 0 | 0:00:00 |
Oct | 0 | 1 | 0 | 0 | 0:00:00 |
Nov | 0 | 1 | 0 | 0 | 0:00:00 |
Dec | 0 | 1 | 0 | 0 | 0:00:00 |
- | 0 | 0 | 0 | 236779 | 0:00:00 |
When I change the date to Date(Floor(ActualStartDate) I get the following:
Month | sum( {<DateType={Order}>} OrderCounter) | sum( {<DateType={Shipment}>} ShipmentCounter) | sum( {<DateType={Order}>} Quantity) | Appointments | Travel Time |
Total | 6 | 11 | 621 | 236779 | 1959:03:00 |
Jan | 2 | 1 | 203 | 219481 | 0:00:00 |
Feb | 1 | 3 | 103 | 219750 | 0:00:00 |
Mar | 3 | 0 | 315 | 233238 | 0:00:00 |
Apr | 0 | 1 | 0 | 230957 | 0:00:00 |
May | 0 | 0 | 0 | 231043 | 0:00:00 |
Jun | 0 | 1 | 0 | 227118 | 0:00:00 |
Jul | 0 | 0 | 0 | 233309 | 1959:03:00 |
Aug | 0 | 1 | 0 | 221531 | 0:00:00 |
Sep | 0 | 1 | 0 | 222419 | 0:00:00 |
Oct | 0 | 1 | 0 | 223157 | 0:00:00 |
Nov | 0 | 1 | 0 | 226379 | 0:00:00 |
Dec | 0 | 1 | 0 | 222079 | 0:00:00 |
Thanks,
Darren
You appear ro have some expressions mixed in from the example as well as your data. Are there really related? Please post a sample QVW or your table viewer picture.
In your last comment, it appears that your ActualStartDate field is a timestamp and you need to floor() it to get the integer portion. to make a linkage to the Calendar. This is correct. I would have to see your data model to explan the double counting, but I wouuld start by removeing the sameple expressions from the table.
-Rob
Hi Rob,
I posted this earlier today. I'm unsure how to attached the QVW to this post so I've included it here: http://community.qlik.com/thread/87958?tstart=0