1 Reply Latest reply on Aug 19, 2016 12:11 PM by Benjamin Greene

# Last two weeks data on single click?

Hi All,

I have a question on Weekly Reports.

I have below table and also few of the charts based on the current week data.

1) I have build this below table using community people.

I have field called "Week" which consists of Week 1,Week 2,Week 3 ,..... Week 52. I have prepared this table to get the "current week" data and "last week" data using the below formula.

For "T Week":    if [Date]= {FIXED : MAX([Date])} then Sales else 0 END

For "L Week":    If [Date]-7= {FIXED : MAX([Date])} then Sales else 0 END

For Variance :   Sum([T Week])-sum([L Week])

For Variance% : (sum([T Week])-sum([L Week]))/sum([L Week])

My question is :

• Example: 1

Suppose I clicked on Week-4. on Filter of "Week" field Table should display the  data of Week 3 and week4 . Under “Variation field”, it should give the difference Week 4- Week 3. Under “Variation%” field, it should give the percentage difference of Week 4 & Week 3.And, the graphs should displayed based on Week -4 data because I selected 4.

Example: 2

Suppose I clicked on Week-12. on Filter of "Week" field Table should display the  data of Week 11 and week12 . Under “Variation field”, it should give the difference Week 12- Week 11. Under “Variation%” field, it should give the percentage difference of Week 12 & Week 11.And, the graphs should displayed based on Week -12 data because I selected 12.

The field "Week" filter should have only single option click.

Please let me know if you don't understand any part of the above questions/statements.

Could someone help me on this? I would be happy If I get some inputs on this.

Thank you.

Best Wishes,

Sandeep

• ###### 1. Re: Last two weeks data on single click?

You could definitely accomplish this with a parameter. I can't give you an exact solution without a sample packaged workbook to work with, but I can try to explain it so that you can work it out. First, you're going to want to right click your date field and select Create>Parameter and format the parameter as week numbers, then show the parameter control. Then, you'll want to create a calculated field based on that parameter that you can use as a filter. Again, I can't say for sure what the syntax will be, but it should be something along the lines of:

IF DATEPART('week', [Date])=DATEPART('week', [Date Parameter])

OR DATEPART('week', [Date])=DATEPART('week', [Date Parameter])-1

THEN "YES"

ELSE "NO"

END

Then, just filter on the "YES" values and you should be good to go.