Aggr Count distint

[#1]

Hi guys,

I have to get an aggregate count distinct over time.

I wrote this expression:

rangesum(above(sum({<Month={'<=$(=max(Month))'}>}aggr(distinct count({<Month={'<=$(=max(Month))'}>} distinct [Goods Supplier]),[Goods Supplier])),0,RowNo()))

It works very well on my local pc (even with web view on), but it is totally messed up when published on the access point (see attached screen...this is not the shape I see on my pc).

This is a really mind blowing problem to me...I thought it was an object bug, but is seems not to be the case.

Any idea/suggestion on that?

Many thanks.

Matteo

[#2]

Yes, they do.

I actually have no idea on how to manage this issue.

[marcus_sommer]

Is section access is enabled in this application?

- Marcus

[swuehl]

What about the AsOf table?

This will link a month to YtD data. A count distinct should show you the correct YtD distinct suppliers then.

No hassle with the subtleties of the Aggr() function.

[#5]

I have already checked this fact, and this is not the case...

[#6]

Will an AsOf table work well also with uncomplete date format (YYYYMM)?

Many thanks for your collaboration!!

[swuehl]

Not sure what you mean with uncomplete: just using month granularity? That's shown in Henric's blog, AFAIR.

[#8]

Hi Stefan,

I have already implemented the AsOf Table, but I can't figure out how to use it in my chart since I have to show not only YTD values (AsOfMonth can't be my dimension).

If you look at the picture I posted, in fact the blue bar chart shows monthly values, while the Yellow line is for the YTD ones.

[swuehl]

That should be explained in Henric's blog post, too:

"And finally, if you use

Sum({$<MonthDiff={0}>} Sales)

You will get the real, non-accumulated numbers."

[#10]

Hi Stefan,

thanks for your support.

I have already tried to use the set analysis as shown by Henric, unluckily it seems not to work in my case.

Here the expressions (AsOfMonth as the unique dimension):

YTD: num(count(distinct [Goods Supplier]),'#.##0')

MONTHLY: num(count({$<MonthDiff={0}>} distinct [Goods Supplier]),'#.##0')

I get the same result...

Please note that the minimum granularity of my data is YYYYMM (i.e. I am not given days): could this fact be the problem?

[swuehl]

Doubly check that your AsOf table correctly links the AsOfMonth to the other calendar month values.

Note that you should use a set expression like {$<YearDiff={0}>} for the YTD, if you follow Henric's approach.

I assume something gets wrong when you calculate the link table. Try to create table box with your month field, the AsOfMonth field and the diff flag fields. Check for some AsOfMonth values, that the relation to the month field and the flags are created correctly.