topic Re: Total Problem in QlikView

Total Problem

Fri, 21 Mar 2014 12:25:16 GMT

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

Re: Total Problem

Gysbert_Wassenaar — Fri, 21 Mar 2014 13:04:24 GMT

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;

Re: Total Problem

Fri, 21 Mar 2014 13:27:55 GMT

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