Rangesum with two Dimensions

[#1]

Hi.

I´m having a problem with a Rangesum expression and need your help.

I´ve got a pivot table with two Dimensions, Month and Day. Then I´ve got in the expressions my Sales , Sales LAst year, and then my problem appears... I need to have a YTD, that gives me accumulated values for Month and Day when I expand my Day dimension is expanded. That´s when I cannot see what I wish. My Values for YTD Month are fine, and for Accumulated Day granularity are also fine, but with January expanded (or another Month), the other Months do not accumulate, giving me just the Sales for each Month by it self.

I´m using this expression:

=RangeSum(Alt(Above(TOTAL [myexpressionName]), 0), rangesum(above(sum({$<Year={2013}>} (A - B - C)),0)))

My Sales expression is a Calculated sum of A - B - C

Reminding that My Values are fine when Day is not expanded;

My Accumulated Days Values are also fine;

Only my Months that are NOT expanded, that doesn´t Accumulate, giving me just each Month Total.

I´ve tried to Start the expression with Sum(Aggr(...)), but that mess with my values and does not give me what I need.

Thanks in Advance!

Best Regards,

Bruno Silva.

[jolivares]

Try this two expressions:

Sales: Sum(Sales)

Acc_Sales:

If(Dimensionality()=0, [Sales],

  If(Dimensionality()=1,

      Sum(Aggr(RangeSum(Above(TOTAL Sum(Aggr(Sum(ValNetoUSD),Day)),0,RowNo(TOTAL))),Month))

      RangeSum(Above(TOTAL [Sales],0,RowNo(TOTAL)))

  )

)

See that you need to use dimensionality function to do  this.  If this not work, try to post a qvw with some data,

[#3]

Good Morning Juan.

I tried what you suggested, but it´s not working very well.

When looking at Day Dimension, it´s fine, but at Month Dimension, the values are all wrong. I´ve tried some things, but cannot solve it!

Here is an example of the pivot table:

Thanks!

Bruno Silva.

[jolivares]

Check this...

[#5]

Hi Juan.

I appreciate your help, but my problem persists...

The problem is that my client wants to be able to view accumulated Month, even with one Day dimension expanded in a Month.

Right now what happens is that when you expand Day Dimension in a Month, ALL other Months appears as 0. Without values. Imagine that you have 250 as total for January, and 150 for February.

Jan     |Day     |Sales     |AccSales

          |1         |100        | 100

          |10       |100        | 200

          |15       |50          | 250

Feb                | 150        | 400      (This Month has Day Dimension Collapsed)

Right now what you see when Day Dimension is Expanded in February is Feb     |150     |0.

I think that this clarifies what my client wants to see.

Best Regards,

Bruno Silva.

[jolivares]

See the attached example, I export and use your data.

[#7]

Hi Juan.

It works fine in your example, but not in my data. I´m missing something...

[#8]

Good Morning Juan.

I found out the problem that makes your example incompatible with my data...

When you exported the data to Excel, you have deleted 2012 data, therefore it remains only 2013 in the file, and like that your example did work, but in my data with 2012 and possible other Years, it doesn´t work.

Any suggestions to solve this? I´ve tried a million possibilities, and nothing solve it!

Best Regards,

BS.

[jolivares · Accepted solution]

Now you have two years...

[#10]

Thank you very much for your help. Your example is working just fine!

Just tell me something... How did you aggregate the data to date? When I first send you my sample Qvw, the data wasn´t aggregated. Did you use "Group by" in script with Sum(Values) as Sales (...) Group by Date? Or did you done that in Excel when you exported the data?

Thanks again for your help and patience!

Best Regards,

Bs.