
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Valuelist and Aggr - alternative for nodistinct
Hello everyone,
I want to create a graph with dimensions created by the valuelist function. The issue is my calculations are Aggr functions. I already read that combining these functions gives errors, which can be solved by addinng nodistinct after the aggr statement.
My issue is that adding the nodistinct statement gives me a different result than when i get the result for the function without nodistinct. Are there other ways to bypass this problem?
My dimension function:
=valuelist ('1-4 weeks ago','2-5 weeks ago', '3-6 weeks ago', '4-7 weeks ago', '5-8 weeks ago', '6-9 weeks ago', '7-10 weeks ago')
My measure function:
IF (ValueList ('1-4 weeks ago','2-5 weeks ago', '3-6 weeks ago', '4-7 weeks ago', '5-8 weeks ago', '6-9 weeks ago', '7-10 weeks ago') = '1-4 weeks ago',
avg (aggr (distinct(count ({$<[Driven Kilometers]={'0'},[Date.autoCalendar.WeeksAgo]={">0<=4"}>}[Driven Kilometers])-4)/24,Asset_Number)),
IF (ValueList ('1-4 weeks ago','2-5 weeks ago', '3-6 weeks ago', '4-7 weeks ago', '5-8 weeks ago', '6-9 weeks ago', '7-10 weeks ago') = '2-5 weeks ago',
avg (aggr (distinct(count ({$<[Driven Kilometers]={'0'},[Date.autoCalendar.WeeksAgo]={">1<=5"}>}[Driven Kilometers])-4)/24,Asset_Number)),
IF (ValueList ('1-4 weeks ago','2-5 weeks ago', '3-6 weeks ago', '4-7 weeks ago', '5-8 weeks ago', '6-9 weeks ago', '7-10 weeks ago') = '3-6 weeks ago',
avg (aggr (distinct(count ({$<[Driven Kilometers]={'0'},[Date.autoCalendar.WeeksAgo]={">2<=6"}>}[Driven Kilometers])-4)/24,Asset_Number)),
IF (ValueList ('1-4 weeks ago','2-5 weeks ago', '3-6 weeks ago', '4-7 weeks ago', '5-8 weeks ago', '6-9 weeks ago', '7-10 weeks ago') = '4-7 weeks ago',
avg (aggr (distinct(count ({$<[Driven Kilometers]={'0'},[Date.autoCalendar.WeeksAgo]={">3<=7"}>}[Driven Kilometers])-4)/24,Asset_Number)),
IF (ValueList ('1-4 weeks ago','2-5 weeks ago', '3-6 weeks ago', '4-7 weeks ago', '5-8 weeks ago', '6-9 weeks ago', '7-10 weeks ago') = '5-8 weeks ago',
avg (aggr (distinct(count ({$<[Driven Kilometers]={'0'},[Date.autoCalendar.WeeksAgo]={">4<=8"}>}[Driven Kilometers])-4)/24,Asset_Number)),
IF (ValueList ('1-4 weeks ago','2-5 weeks ago', '3-6 weeks ago', '4-7 weeks ago', '5-8 weeks ago', '6-9 weeks ago', '7-10 weeks ago') = '6-9 weeks ago',
avg (aggr (distinct(count ({$<[Driven Kilometers]={'0'},[Date.autoCalendar.WeeksAgo]={">5<=9"}>}[Driven Kilometers])-4)/24,Asset_Number)),
avg (aggr (distinct(count ({$<[Driven Kilometers]={'0'},[Date.autoCalendar.WeeksAgo]={">6<=10"}>}[Driven Kilometers])-4)/24,Asset_Number))
))))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not sure if you have read this or not, but this will be a good read on the topic
Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies
Also, can you use Island table instead of ValueList as that might be able to resolve your issue


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
maybe use function div(Date.autoCalendar.WeeksAgo -1)
1 to 4 gives 0
5 to 9 gives 1
and so on
regards


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I could imagine that The As-Of Table could be useful to handle your overlapping clusters.
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have read this blog and tried to create an aggr dimension, but I don't understand the idea completely and could not get it to work

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you give any further explaination, because i do not understand what the div function does?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes i thought of this as well and this will probably work. However until now I didn't manage to create an As-Of table. For now it's probably a little too much for my scripting skills.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
div(x,y) ~ floor(x/y)
so it gives you directly the interval without test each time of :
Date.autoCalendar.WeeksAgo]={">1<=5"}>

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is there a sample you can share to help you better?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here a very simple example how it might be done:
Calendar:
load *, rangemax(week(today(0)) - Week, 0) as WeekAgo;
load *, month(Date) as Month, year(Date) as Year, week(Date) as Week;
load date(floor(yearstart(today(0))+recno()-1)) as Date autogenerate 365;
Facts:
load rand() * 50 as Value, date(floor(yearstart(today(0))+ceil(rand()*365))) as Date autogenerate 1000;
AsOf:
load
Week, pick(Cluster, dual('0-4', Cluster), dual('1-5', Cluster), dual('2-6', Cluster),
dual('3-7', Cluster), dual('4-8', Cluster), dual('5-9', Cluster), dual('6-10', Cluster)) as Cluster;
load Cluster, Week - (iterno() - 1 + Start) as Week while Start + iterno() - 1 <= End;
load *, week(today(0)) as Week, recno() as Cluster inline [
Start, End
0,4
1,5
2,6
3,7
4,8
5,9
6,10
];
I think with a bit playing you could adapt it to your real datamodel. Essential is just to duplicate the KEY (here Week) between the tables so often as cluster-range is which is here done with a while-loop and then to use this new dimension for the charts.
- Marcus
