Visualization with qty sold by year by week

[rteruyas]

Good day

I'm struggling with one of the visualizations I need to create, so I'm here in look for some advice.
I have a dimensional model

[Date dimension]
* Year
** Quarter
*** Month
**** Week
***** Date

[Measures]
Qty Sold

I'm trying to create a visualization to have
Week as x-axis (1 to 52-53 depending of the year)
Qty sold by year as measure/value

Ideally, I'd have one line with a different color for each one of the years with data representing qty sold, running from week1 to week53.
Is there any way to represent this?

Thanks in advance
Regards

[CognosPaul]

In a case like this your model doesn't match your needs. Do you have access to the model? Create a secondary hierarchy containing only week of year (1-53). This way it will be a simple matter of dragging the week level to the x axis and the year level to the y axis, with Qty as measure.

[rteruyas]

Thanks for the fast response.

Unfortunately, I don't have access to the model to create the hierarchy myself. Will try to get in touch with the person that created it, though.
Meanwhile, I'll try see if I find a workaround

[AnalyticsWithJay]

Meanwhile, I'll try see if I find a workaround

Query 1: Quantity and Year
Query 2: Week and Year

Query 3: Local Join in Report Studio of Query 1 and Query 2 on Year.

Point your visualization to Query 3. Make sure you limit the data in Query 1 and Query 2 to the absolute minimum necessary because this solution doesn't perform as well. I haven't tested, but this should only work when using levels -- not attributes.

[CognosPaul]

Problem with that is the week members will be unique for the year. You COULD use local processing to aggregate the weeks, but it's an ugly ugly solution. Really, the best solution is to make sure the model matches the needs. I always try to ensure there are separate hierarchies for Quarters/Months and Weeks.

If you don't mind providing a static number of years, you could show the current year's weeks and use a set of parallelperiods. The drawback is each line needs to be defined manually. Take a look at the attached report.

[Invisi]

Why do you want to have the yearly sold quantity on a weekly basis?