topic Cannot create a YTD YearWeek straight Table in QlikView

Cannot create a YTD YearWeek straight Table

tabletuner — Mon, 01 Feb 2010 23:33:49 GMT

Hi Gurus,

I've got a big problem concerning YTD dates in a straight table. My source table contains sales records at yearweek level. For convenience i added for each record the week that lies 52 weeks back in history.

In the final straight table i would like to see 4 columns: YearWeek, Site, Sales en Sales YTD. (Sales YTD based on 52 weeks in history).

Please take a short look at the example that i posted. It will make the problem easy to understand. I hope someone can come up with a solution quick!

Best regards,

Tjeerd

Cannot create a YTD YearWeek straight Table

tabletuner — Tue, 02 Feb 2010 14:02:08 GMT

Does anyone have an idea yet? My customer is in quite a hurry...

Regards,

Tjeerd

Cannot create a YTD YearWeek straight Table

Wed, 14 Apr 2010 16:33:08 GMT

Hi Tjeerd,

I've been trying to do something similar using set analysis. So far I have been unsuccessful because as far as I can tell, a chart dimension can not be referenced in a set analysis expression. This is because a 'set' is evaluated once for the entire chart, rather than for each row.

To acheive what I think you were after in your application, I manipulated the data during the load stage i.e. in the script.

I produced a second 'YTD' sales figures table, then linked the new table to your existing table using a link table which maps each 'current week' and corresponding site ID, to the relevant YTD week and site ID.

Hopefully this will be obvious enough from the modified application which I've attached to this post.

I hope I've correctly understood your requirement, and that my suggestion is of some use to you. I think it's far from ideal, as it increases the amount of data being loaded, and requires a more specialized script. However, it's perhaps better than nothing

Regards,

Alex

Cannot create a YTD YearWeek straight Table

tabletuner — Thu, 15 Apr 2010 15:37:53 GMT

Hi Alex,

First of all many thanks for the energy and time you took to help me out with this one!

Your solution proves to be right in your miniqvw. I wish i read yourpost earlier. In the meantime i solved the problem by joining the transaction table on itself. During the join, I increased the date with 364 days. The join resulted in one extra field for the table called Sales YTD.

I think your solution is preferable because I don't like to transform the transaction table.

Regards,

Tjeerd