Want to see number of weeks data for multiple stores in Line Chart

[#1]

13-week data for multiple stores issue in Line Chart

I have a line chart with 2 dimensions – week & stores

In this chart for any current selection I need to be able to see data upto 13 weeks prior to currently selected week and at the same time , if more than one store is selected, line chart must show 13 weeks data for both stores as separate charts.

At the moment I am able to achieve only either of this with set expressions

With $, I am able to include multiple stores but my 13 week criteria does not work ( I use the following expression)

Sum({$<WeekStore={"<=$(=(vCurrentWeek))>$(=(vCurrentWeek)-13)"}>}Score)/7

With 1, I am able to select multiple weeks but cannot achieve multiple store selection ( I use the following expression)

sum({1<WeekStore={"<=$(=(vCurrentWeek))>$(=(vCurrentWeek)-13)"},BUSK ={$(vStore)}>}Score)/7

Above score is the numeric value which is being summed over the chosen dimensions

Can someone point out the mistake or guide as to how I can achieve both at same time.

[Gysbert_Wassenaar]

Can you post a document that demonstrates the problem?

[#3]

Hi Gysbert,

Yes, pfa app attached. i am talking about the expression under the line chart called "Weekly Score". The first one on top on the sheet.

I have tried various combinations, but none of them seem to work

I wonder where i am going wrong.

Thanks

Regards

Sundar

[Gysbert_Wassenaar · Accepted solution]

Your variable vStore is defined as =BUSK. Since you didn't specify an aggregation function the only() function is used. So your variable vStore is actually =only(BUSK). The only() function can only return one value. Which is why your expression doesn't work when you select multiple StoreLocations.

You seem to want to disable all selections, but want to use the possible values of BUSK resulting from your selection of StoreLocations. If so you can use the P() function in the set modifier:

sum({1<WeekStore={"<=$(=(vCurrentWeek))>$(=(vCurrentWeek)-13)"},BUSK=P(BUSK)>}Score)/7

[#5]

Hey Gysbert,

Just as i was reading up on this function as a last try, your reply was posted!

This did solve the problem.

Thanks for your speedy help.Much appreciated.

Regards

Sundar