topic Re: Valuelist and Aggr - alternative for nodistinct in App Development

Valuelist and Aggr - alternative for nodistinct

pascaldijkshoor — Thu, 31 May 2018 12:25:02 GMT

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))

))))))

Re: Valuelist and Aggr - alternative for nodistinct

sunny_talwar — Thu, 31 May 2018 12:26:27 GMT

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

Re: Valuelist and Aggr - alternative for nodistinct

ogautier62 — Thu, 31 May 2018 12:30:45 GMT

Hi,

maybe use function div(Date.autoCalendar.WeeksAgo -1)

1 to 4 gives 0

5 to 9 gives 1

and so on

regards

Re: Valuelist and Aggr - alternative for nodistinct

marcus_sommer — Thu, 31 May 2018 13:17:08 GMT

I could imagine that The As-Of Table could be useful to handle your overlapping clusters.

- Marcus

Re: Valuelist and Aggr - alternative for nodistinct

pascaldijkshoor — Thu, 31 May 2018 14:44:02 GMT

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

Re: Valuelist and Aggr - alternative for nodistinct

pascaldijkshoor — Thu, 31 May 2018 14:44:34 GMT

Can you give any further explaination, because i do not understand what the div function does?

Re: Valuelist and Aggr - alternative for nodistinct

pascaldijkshoor — Thu, 31 May 2018 14:49:13 GMT

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.

Re: Valuelist and Aggr - alternative for nodistinct

ogautier62 — Thu, 31 May 2018 15:14:54 GMT

div(x,y) ~ floor(x/y)

so it gives you directly the interval without test each time of :

Date.autoCalendar.WeeksAgo]={">1<=5"}>

Re: Valuelist and Aggr - alternative for nodistinct

sunny_talwar — Thu, 31 May 2018 15:19:43 GMT

Is there a sample you can share to help you better?

Re: Valuelist and Aggr - alternative for nodistinct

marcus_sommer — Fri, 01 Jun 2018 07:49:41 GMT

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