if we apply action filter on sheet1 for a particular week (ex; week 9), the data on sheet2 will be available for that particular week9 only. so we will not be able to display previous weeks value. we should definitely use parameters.
I have tried this using simple year trend from superstore. I am actually looking out for the options where I can store and use it. Once I click on the Year-2015 it should give me 2015 or 2015-1 year in the view.
I am stuck and looking out for the solution for this.
Any help is appreciated. (Mark Fraser, can you please help in this??)
Thanks in Advance.
Sales example.twbx 361.3 KB
I don't know of any way show this week and last week in a single target chart. As action filters only seem to use an = operator I don't think it's possible but I'd love to be proven wrong.
If you are willing to have two target charts, this week in one and last week in the other, there is a workaround to accomplish this. If you think this worth pursuing I can explain the process to you.
Awesome that this will work for you and that you figured it out without any additional explanation but I'm going to post the process for others who may stumble across this post.
1. Create a calculated date to use in your action filter. Last Week: DATEADD('week', 1, [Order Date])
2. Create Sales by Week worksheet in the example it is named SRC. (This is your source for the actions)
3. Create Current Week Sales worksheet, in the example this sheet is called TGT.
4. Create Last Week Sales worksheet. You will want to uncheck Show Header from the DAY(Last Week) pill. In the example the sheet is called TGT Last Week
5. Layout all the sheets on a dashboard
6. Create the action for Current Week Sales
7. Create the action for last week sales. You need to use selected fields rather than all fields. The Source field value will be WEEK(Order Date) and the Target field will be WEEK(Last Week)
Now when you click on a week in the source sheet the current weeks data will be displayed by day in TGT and Last weeks data will be displayed in TGT Last Week.
Attached is the twbx.
Sales example.twbx 771.5 KB
1 of 1 people found this helpful
Here's what I'm thinking...
You could create a COPY of the data source, and create a calc in that copy that adds 1 to the week value. DATEADD('week',1,[Date Field])
Then you blend the original source with the COPY source, hooking [Date Field] from the ORIGINAL source with [Date-plus-one-week] from the COPY source.
Now you can display current week in the target sheet, and SUM([COPY source].[whatever field]) from the copy source, and you'll get current week data from the original source, and last week's data from the copy source.
Thanks Ivan for taking out some time for this. I have achieved the same thing but in my solution I have two sheets which are showing all the week data. Whenever I click on a week in the first sheet where the action has been initiated then only current and previous week data is displayed. Is there any way to make the current and previous sheet show default data for any particular week and whenever any week is selected in the first sheet the other two sheets get updated to that respective week. Is it possible?
Thanks Joe, I will try this and get back to you.