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

[#2]

Anyone who can help me with this? I've been reading this article: Calculating rolling n-period totals, averages or other aggregations among others, but can't manage to solve it.

[tresesco]

Aggr() can be helpful. See the attached sample.

[#4]

Thank you tresesco for your help! Your example is working fine, but when I try to incorporate it in my expression I fail. Are you able to say how it should look?

This is my current expression:

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

I tried to edit it like this, but that didn't help:

=Before(Only({1}Aggr(Count({$<TaskStatus={'New'}>}TaskStatus),Month)))

[SunilChauhan]

Month= in set analysis means to exclude only selection not to exclude data i.e Month

[tresesco]

Could post your sample qvw?

[#7]

Unfortunately that is difficult as it is rather large with data I can't share. But the expression I'm using now is:

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

This is in a pivot table where the vertical dimensions are Year and Month, horizontal dimension is Country.

[#8]

Thanks but I don't quite understand what you mean. How do I need to change the expression then?

[tresesco]

See this: How to upload sample qvw

By the time try:

=Before(Only({1}Aggr(Count({$<TaskStatus={'New'}>}TaskStatus),Year,Month)))

[#10]

Hi,

I had no idea about the scramble functionality, thank you!

Here is the file attached. I'm working on the second table ("Calculated data"), specifically the expression called "Target". In this file I'm using week instead of month as dimension.

The expression you proposed didn't work.