Accumulating an expression beyond a restricted/calculated dimension

[#1]

Hi all,

I have a testing status dashboard that shows the numbers of planned test cases to be passed versus the actual test cases passed. There are two straight tables to show these numbers…one is a long term plan that shows all dates and the other is the short term plan (which is 3 days back and 7 days forward).

I’m not having any issues with the long term plan since it includes all of the dates…

…but the short term plan does not accumulate correctly because the dates displayed are restricted with the following calculated dimension:

Date =Date(If([Test Plan Day]=(Today()) OR [Test Plan Day]=Today()+1 OR [Test Plan Day]=Today()+2 OR [Test Plan Day]=Today()+3 OR  [Test Plan Day]=Today()+4 OR  [Test Plan Day]=Today()+5 OR  [Test Plan Day]=Today()+6 OR [Test Plan Day]=Today()+7 OR [Test Plan Day]=Today()-1 OR [Test Plan Day]=Today()-2 OR [Test Plan Day]=Today()-3,[Test Plan Day]))

In my first attempt, I used the following expressions and received the results shown. As you can see, it accumulates correctly for the dates shown, but what I need is for it to accumulate back to very first date of the complete plan even though it’s not displayed in the table.

Actual per Day =Sum(If([Test Status]='Passed', ROWCOUNTER))

Actual Cumulative =Sum(If([Test Status]='Passed', ROWCOUNTER))  with Full Accumulation turned on

In my second attempt, I used these expressions and received the results shown. Now it accumulates correctly back to the first date of the plan, but it doesn’t show the correct number for each line since I’m using the total of the ‘Actual per Day’.

Actual per Day =Sum(If([Test Status]='Passed', ROWCOUNTER))

Actual Cumulative =Sum(TOTAL(If([Test Status]='Passed', ROWCOUNTER)))  with No Accumulation

Can anyone explain to me how I can accumulate back to the first date the plan and still show the correct number for each line so that it matches the numbers in the long term plan table?

It’d be greatly appreciated. Thanks!

[Gysbert_Wassenaar]

You may need some kind of AsOf table. See this document:

• Calculating rolling n-period totals, averages or other aggregations

If you need more help try to create an example document:

• Preparing examples for Upload - Reduction and Data Scrambling