Showing only certain results in Pivot Table

[picturebox]

Hello,

I have sales data for 300 products over 106 weeks. To find out which products have not sold during the last 4 weeks I sum the sales data for the last 4 weeks. In the pivot table at the moment I get all the sums showing including those with 0 sales. What I want to do is to show ONLY the products that have zero sales, as an exception report.

I cannot seem to get this to work.

Appreciate any help on this!

Thanks,

Petra

[Miguel_Angel_Baeyens]

Hello Petra,

If I understood you right, you want to show all records except for those that have sales for the last four weeks. If so, you can use

{1 - < SalesDate = {">=$(=AddMonths(Today(), -1))"} >}

Hope that helps

[picturebox]

mmmhh,

not quite!

I calculate the total sales using this ...

sum( { $ <WeekIndex = {1,2,3,4} >} UnitMovement )

where my weekindex is set to go backwards, so that 1-4 are the 4 weeks I am summing. Once I have the result, I want to show ONLY those that have summed to zero ..

Not sure how to control the "show only" in a pivot or straight table ...

Do I make sense?

Thanks,

Petra

[#4]

The best way to display only certain values is to add a condition.

You can add your condition to the dimension, or make a trick so that your results equals to 0 returns somenting (a string of your choice) and results differents than 0 returns "null".

See the 2 exemples in the attached file.

[#5]

I can't upload the file with the forum

Here is an external link :

http://www.megaupload.com/?d=M54AQM2S