distinct values for each month?

[tgs728]

HI all,
I have a requirement like below

category   month    id

ab               1           1
ab                1            2
AB               2             2
AB               2             3
AB              3               1
AB               3             2
AB               3           4


sO I WANT TO DISPLAY ONLY NEW VALUES FOR EVERY MONTH SO MY DESIRED OUTPUT SHOULD BE

category   month    id

ab               1           1
ab                1            2
AB               2             3
AB               3           4...


Please help me how to achieve this.need an urgent help..i am out of time..

Thanks ,

TGS

[navissar]

Hi,
This is an interesting requirement.
You did not specify whether you were over relational or dimensional. Due to the nature of your question I'll assume the first, note if you're on dimensional this method will not work, you'll need another way.
So, let's start with the basic scenario. I've recreated something similar over samples:

I marked in red the new rows I'd expect to see (The order method that appear for the first time). I'd expect the first month, and then only the rows in red.
Let's add the following data item, I named it "Order Method Index":

Code Select Expand

running-total(1 for [Order method type])
This will break down every occurrence of Order Method, and perform a running-total. See result:

But the interesting feature is what happens when a new month begins - see:

When an order method that was already on a previous month is displayed on a new month, the enumeration continues. When a new Order Method is introduced, it is enumerated from the start (=1).
Next step - add a filter with the following:

Code Select Expand

[Order Method Index]=1
IMPORTANT: set the filter to operate AFTER AUTO-AGGREGATION.
Now we run the report and....



This should do it.
On a different note:

Please help me how to achieve this.need an urgent help..i am out of time..

Please don't do this. It may be perceived as rude and/or pushy. Please refer to the forum etiquette post section 2 (http://www.cognoise.com/index.php/topic,24030.0.html)

[tgs728]

Great idea... Thanks!!!!!
can you please tell me what happens running-total(1 for [Order method type])
normaly running total will add every row but how it break down for each order method.

Thanks
TGS

[navissar]

I'm sorry, I didn't understand that. Could you please explain your question again?