Help creating a pivot table

[goncalo_ricardo_pereira]

Hello,

I´m trying to create one pivot table, but i´m having some problems...

I´m going to explain my problem using one example.

This is my pivot table right now:

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
Value 1	0			3					8
Value 2	0			4				6

I'm trying to use the function before() or after(), but the only problem is that i can get only the last value or the next value, creating my table like this...

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
Value 1	0	0		3	3				8	8
Value 2	0	0		4	4			6	6

This is the pivot table i´m trying to create.

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
Value 1	0	0	0	3	3	3	3	3	8	8	8
Value 2	0	0	0	4	4	4	4	6	6	6	6

Is there any way to do this?

Thanks for the help.

[#2]

If you can put the file would help

[Anonymous]

Goncalo

As Octavio implied, it is pretty hard to guess what your underlying data really is and what you have done without you providing your qvw, but you could try this:

• In your Chart [Pivot Table] Properties
• Presentation tab
• Bottom left hand corner you'll see
  • Null Symbol
  • Missing Symbol
    • Try setting one or both to 0

Best Regards,     Bill

[MayilVahanan]

Hi

Try  in script itself like

Load *,

if ( Isnull(Value1), Peek('Value1'), Value1) as Value1

from tablename

Order by Week;

[goncalo_ricardo_pereira]

Thanks for all your help.

I attach one example as you request.

This is the pivot table i´m trying to create.

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11
Value 1	0	0	0	3	3	3	3	3	8	8	8
Value 2	0	0	0	4	4	4	4	6	6	6	6

Yes is possible to do this in the script... but before that is there a way of doing this using only the expression of the pivot table?

Thank you for you help.

[#6]

Your problem is not the design of the table if the expression you are using.

could you tell me what you want with that expression you intend to represent

[goncalo_ricardo_pereira]

Basically I´m trying to do one expression that allows me to fill all the 0 values with the last value > 0 presented in the same row.

For example:

in the first line (Value 1) between week 4 and week 9 i have:

week 4     week 5     week 6      week 7     week 8     week 9

   3              0              0             0              0                8

In the week 5 to 8 i have 0, but in reality i want that week 5 to 8 get the same number of week 4, because its the previous value > 0.

If its possible using only the expression i will get this result:

week 4     week 5     week 6      week 7     week 8     week 9

   3              3              3             3              3                8

Expression i´m trying to do:

if(columnno( ) = 1 and Sum(Value) <> 0, Sum(Value), get the previous value > 0)

[andrewpettit]

Two suggestions that might be helpful.  One it looks like your really trying to do a running total so have you tried using the "Full Accumulation"?  Second perhaps Try "RangeSum( Before( [Week Col] ) , Sum( [ Week Val] ) )" the RangeSum will ensure that if the before column is null it will be treated as a "0" zero value and thus not cause the current calculation to return a null as well.

[goncalo_ricardo_pereira]

Hello Andrew,

I´m not trying to do a "full accumulation". The "RangeSum" gives me one problem when i reach week 10 if in this week i have a value = 0:

using RangeSum example:

week 4     week 5     week 6      week 7     week 8     week 9     week 10

   3              3              3             3              3                8             11

But in fact my desire result is:

week 4     week 5     week 6      week 7     week 8     week 9     week 10

   3              3              3             3              3                8             8

Another example:

Lets imagine i have this values:

week 4     week 5     week 6      week 7     week 8     week 9     week 10     week 11     week 12

   3              0             0             5              0                8             0               4               0

I´m trying to get an expression that gives me this result:

week 4     week 5     week 6      week 7     week 8     week 9     week 10     week 11     week 12

   3              3             3             5              5                8             8              4               4

Basically, when i get a week that have a value = 0 i want to get the previous value > 0.

I already tried to use the rangemaxstring, but in this case i get this result:

week 4     week 5     week 6      week 7     week 8     week 9     week 10     week 11     week 12

   3              3             3             5              5                8             8              4               8

And its wrong, because when i reach week 12 and the value = 0 i need the previous value > 0 and that value is in week 11 with a value = 4.

Once again thanks for your help

[andrewpettit]

so maybe what you need is more like this?

if( RangeSum( myExpression ) > 0 , myExpression , Before( [myField] ) )