Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Total Problem

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.


trucks.png

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

2 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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;


talk is cheap, supply exceeds demand
Not applicable
Author

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