Including unselected data in before-function

[#1]

I've got a pivot-table with, among others, the below expression. The dimensions are country and month.

=before(TOTAL count({$<TaskStatus={'New'}>}TaskStatus))-before(TOTAL count({$<TaskStatus={'Cancelled'}>}TaskStatus))

This counts the difference between New and Cancelled tasks from last month. The formula works fine except if I make a date selection, then the first month in the selection becomes blank as there is no data for the before()-function. I can force an inclusion of the data (Month=) in the set analysis, but then the table displays all months.

So I want to hide the unselected values in the month-field, but still include them in the calculation. How do I do this? All other filters should still affect the calculation.

An example, the table without selections looks like this:

Dec 2013	Jan 2014	Feb 2014
2	5	4

So when selecting 2014 the table should look like this:

Jan 2014	Feb 2014
5	4

With my current formula it looks like this when selecting 2014 (wrong):

Jan 2014	Feb 2014
-	4

[tresesco]

Gustav,

I have been trying with your attached sample and it has been couple of hours in pieces since that day. Finally I found it that the Before() is not actually behaving properly with Aggr(), while it's counter-part Above() does. I am attaching a sample qvw that shows the issue. The straight table works fine but the pivot. It's strange for me.

[#3]

Thank you a lot tresesco for trying. I guess I will just need to include a textbox in my report, notifying people the first period in the selection will always be blank.

[tresesco]

No, wait ! Got an alternative. Use fields TaskBu,Year,Week as dimensions, expression:

=Aggr((Above(TOTAL count({$<ReportStatus={'New'}>}ReportStatus))-Above(TOTAL count({$<ReportStatus={'Cancelled'}>}ReportStatus))),TaskBU,Year,Week)

[#5]

Thank you tresesco, this looks like great progress! I changed references from Year to WeekYear. However, it still doesn't work (although now it displays something as opposed to nothing):

If you select years 2013 & 2014 then the first week of 2014 is correct.

If you select year 2014 then the first week of 2014 is incorrect (a much bigger figure).

[tresesco]

When you chnaged the dimension, hope you made the similar change in the expression like:

=Aggr((Above(TOTAL count({$<ReportStatus={'New'}>}ReportStatus))-Above(TOTAL count({$<ReportStatus={'Cancelled'}>}ReportStatus))),TaskBU,Year,WeekYear)

And, if this doesn't take you to the goal, it has to be something with your fields when getting calculated at the backend.

[#7]

Yes I did, I also tried setting only TaskBU and WeekYear as dimensions, and "=Aggr((Above(TOTAL count({$<ReportStatus={'New'}>}ReportStatus))-Above(TOTAL count({$<ReportStatus={'Cancelled'}>}ReportStatus))),TaskBU,WeekYear)" as expression.

But even so, you can see for yourself that the values for week 1 year 2014 in the target-column changes depending if you have 2014 selected or 2013 & 2014.