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

    Last two weeks data on single click?

    Sandeep Kotha

      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?
          Benjamin Greene

          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.