Solved: Cannot create a YTD YearWeek straight Table - Qlik Community

tabletuner · ‎2010-02-01

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

Report Inappropriate Content · ‎2010-04-14

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

View solution in original post

tabletuner · ‎2010-02-02

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

Regards,

Tjeerd

Report Inappropriate Content · ‎2010-04-14

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

tabletuner · ‎2010-04-15

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