Count Distinct Accumulated

[#1]

I’m trying to count suppliers for each month for current year and two past years.  I want it accumulated on months.
The goal is to decrease number of suppliers.

I do it like this --> count( {$<Year={$(=year(today()) - 1)}>}DISTINCT  [Supplier Name] ) with Month as diminsion.

My problem is that I want to have it DISTINCT for each accumulated step.   February should show jan&feb DISTINCT.

If I use Accumulate in the chart properties I get doubles, tripples and so on.

For february I want count(Distinct Supplier Name) for jan and feb.  For march I want count(Distinct Supplier Name for jan,feb and march.   ...and so on.

Hope you understand me

// Fjalar

[#2]

I've done this in my script.  This is in right direction but nut perfect.  If anyone have some nice solution then feel free to add it to this conversation

tAsOfMonth:

Load distinct

  Month

Resident Time

Order By Month;

AsOfMonth:

LOAD Distinct

  Month,

    Month - IterNo() as AccMonth

Resident tAsOfMonth While IterNo() <=12;

drop table tAsOfMonth;

[flipside]

Hi Fjalar,

I've been playing about with this problem between other jobs I should be doing, and I wonder if the attached is any use. The way it works is to use a dynamic valueloop and to pick values using an expression that covers the full data range. This allows the user to see supplier patterns in subsets of the data. By creating the long expressions in script, it makes management simple.

I'd be interested in your thoughts.

flipside

[#4]

Hi,

Your solution is above advanced.  Very well done! 

In the end my solution was like this [see below].  Then in the chart, in the Dimension Limits I choose "Show only" First - 12.

tAsOfMonth:
Load distinct
[MonthNum]
Resident Time
Order By [MonthNum];
AsOfMonth:
LOAD Distinct
[MonthNum],
[MonthNum] + IterNo() - 1 as AccMonth
Resident tAsOfMonth While IterNo() <=12;
drop table tAsOfMonth;

left Join(AsOfMonth)
LOAD * INLINE [
AccMonth, AccMonthName
1,Jan
2,- Feb
3,- Mar
4,- Apr
5,- May
6,- Jun
7,- Jul
8,- Aug
9,- Sep
10,- Oct
11,- Nov
12,- Dec
13,Feb -
14,Mar -
15,Apr -
16,May -
17,Jun -
18,Jul -
19,Aug -
20,Sep -
21,Okt -
22,Nov -
23,Dec
];