Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I got the headbreaking task of calculating the average per week of cars we used in division 1 and 2.
As you can see below this the total of cars we used per day and total per week.
->Now I first want a calculation that will count the number of days cars have been used. Like:
Week 1 - 3 days
Week 2 - 5 days
etc
This is so I can divide the total and get the average per week. (Avg formula works but is another way possible)
->Secondly I need those 2 totals of division 1 and 2 to be the total in column 3 so I can see the overall number of average cars per week.
-:> The big problem is these 2 divsions are in the same table(file) so when I would give in DivisionName -> 1 it will show me 2 columns 1 for the divion and the other 1 empty.
Expressions:
sum ({<PerformanceWeekday = {'mo','tu','we','th','fr'}>}counterTrucksDay1)
kind regards
Vincent
1. count(distinct PerformanceWeekday) should return the number of days in a week. So divide by that.
2. Enable partial sums for the DivisionName dimension.
3. Load both division files into the same table:
DivisionData:
load *, 'Division1' as Source from division1source;
concatenate(DivisionData)
load *, 'Division2' as Source from division2source;
thanks gysbert for the response but I have to correct myself both divisions are in the same file not apart so thats the reason you can split them
Also the count distinct of weekdays gives me 5 voor division 1 and 5 for division 2