Graph in SSRS only shows a couple of week's data

Question

I'm making a graph in SSRS which will highlight the sales for each week from a specific week, and from 52 week up to that date.

For this I have created the following data set in SSRS:

WITH SET LAST52WEEKS AS
    {   
        STRTOMEMBER("[Dim Date].[Week].&[2017]&[1]&[1]&[4]").Lag(52):
        STRTOMEMBER("[Dim Date].[Week].&[2017]&[1]&[1]&[4]")
    }
Select {[Measures].[Quantity]} ON 0,
{Last52Weeks} ON 1
FROM (
    Select {
        [Dim Store2].[Store Key].&[1024]
        } on columns
    from [DSV_FactStoreSales 1]
    )

which works as intended, and have an output for week 13 through 4 with each week having a weekly sales Quantity>400 (except for one week with Quantity (null).

However, when I add this data as the Y-axis in a graph in SSRS along with Weeks attribute as the X-axis, only four rows are returned. The rows returned are for the weeks

1,19,3,40

All other values are blank in the graph. Does anyone have any idea as to why this may occur?

Regards,

Cenderze

EDIT updated x-axis from using Interval value = 1

When I changed the Interval value under Horizontal Axis Properties I now get the following values for the x-axis:

1, 13, 14,[...],19, 2, 20, 21, 22, 23, [...], 29, 3, 30, 31, 32,[...],39, 4, 41, 42,[...], 53

where I have bolded the results which seem odd. Im guessing these values are the quarters? I am however using Weeks as my Grouping variable.

EDIT

Following Mike Honey I figured that:

WITH SET LAST52WEEKS AS
    {STRTOMEMBER("[Dim Date].[Date].&[1]&[2017-01-29]").Parent.Lag(52)
    :
    STRTOMEMBER("[Dim Date].[Date].&[1]&[2017-01-29]").parent}
Select {[Measures].[Quantity]} ON 0,
{Last52Weeks} ON 1
FROM (
    Select {
        [Dim Store2].[Store Key].&[1024]
    } on columns
from [DSV_FactStoreSales 1])

ought to work, but it simply returned that Quantity was (null). Why isn't this equivalent to the other Query I wrote, but based off of Date rather than week?

EDIT

I also noted that the graph I am creating has week 52 to the utmost right, whereas it is supposed to have week 4 at the utmost right.

EDIT

EDIT

Edited the Query to become:

WITH SET LAST52WEEKS AS
{STRTOMEMBER("[Dim Date].[Hierarchy].&[2017 W4]").Lag(52):STRTOMEMBER("[Dim Date].[Hierarchy].&[2017 W4]")}
Select {[Measures].[Quantity]} ON 0,
{(Last52Weeks,[Dim Store2].[Store Name].Allmembers)} ON 1
from [DSV_FactStoreSales 1]

where Hierarchy is an Attribute Hierarchy with the values 2010 W1,...., 2018 W52.

But this yields me an output:

2016 W40    Store1  300
2016 W40    Store2  400
...
2017 W39    Store1  400

where I would expect the output to be:

2016 W4 Store1  300
2016 W4 Store2  400
...
2017 W4 Store1  400

Any ideas? It is just an hierarchy with one attribute, Week Name (I've tried the Query with both using [Dim Date].[Hierarchy].[Week Name].&[2017 W4] and [Dim date].[Hierarchy].&[2017 W4], both with the same output.

EDIT (very close now!)

Answer 1

There are two issues I can see with your current chart. One is that your week numbers are being treated as text, which is why the ordering has sequences like 19, 2, 20. The other is that it isn't distinguishing where the week numbers for one year ends and another year starts.

I'm not familiar with mdx. I'm hoping you're able to make a simple dataset that is purely a list of sales with dates. For my solution, I used an Oracle query like:

SELECT date SALEDATE, ID FROM sales
WHERE date BETWEEN '28-MAR-2016' AND '28-JAN-2017'

With a new chart, use [Count(ID)] for the values. It then needs two category groups, first one for the year and then one for the week number. You can use expressions to get these from a date field. For the first category group, set Group on: and Label as

=DatePart(DateInterval.Year, Fields!SALEDATE.Value)

and for the second, set Group on: and Label as

=DatePart(DateInterval.WeekOfYear, Fields!SALEDATE.Value)

To then hide the columns with under 400 sales, right-click the second category group (the weeks one) and add a filter: [Count(ID)] (integer) > 400.

Even if your dataset is different from the raw data approach I'm using, hopefully this will steer you in the right direction.