Aggregating data

[surajap123]

Hi All,

Table:

LOAD Bookingid,

     ProgName,   //Dimension

     Hours as Hrs,

     Weeks as Wks,

     Hours * Weeks as TotalHours,

     Resident ProgramWeeks

     WHERE StatusId = 10)

     order by ProgName;

I have created Table box with below structure which is giving me the desired result.

ProgName| Hrs| Wks| TotalHours|

However, I want to sum up the Hrs, Weeks and TotalHours booked by each ProgName per Day. The Date field is present in the main table based on which the above resident table is created.

Please let me know the best qlikview object and expression i need to write to fulfill it.

[tresesco]

Straight table

Dim1: ProgName

Dim2: Date

Exp1: Sum(Hrs)

Exp2: Sum(TotalHours)

[its_anandrjs]

You can use simple table box by fields  ProgName| Hrs| Wks| TotalHours|

TempTable:

LOAD

     Bookingid,

     ProgName,   //Dimension

     Hours as Hrs,

     Weeks as Wks,

     Hours * Weeks as TotalHours,

     Resident ProgramWeeks

     WHERE StatusId = 10)

     Order by ProgName;

Final:

Load

     Bookingid,

     ProgName,

     Sum(Hrs) as Hrs,

     Sum(Wks) as Wks,

     Sum(TotalHours) as TotalHours,

     Resident TempTable

     Group By Bookingid, ProgName

     Order by ProgName;

and use table box as ProgName| Hrs| Wks| TotalHours|

[surajap123]

Hi Tresesco,

I have created a striaght table using your expressions. I see that straight table is aggregating the data based on Program and Date.

However the expression values are not correct as compared to Table box.

Eg-If you compare the first row in straight table and table box

In straight table in Program field value '0 ee 8 (02 mbfvl/drxz)', the sum(Hrs) value is 420 for Date 16/02/2009.

But in Table box there are 6 rows for date 16/02/2009, so sum of 21*6 = 126.

So, i think the straight table should show 126, instead of 420.

Could you please let me know where i am doing wrong..

@Anand- I cannot use table box because, i want to aggregate the data based on Program and Date.

[tresesco · Accepted solution]

Table box doesn't necessarily show all rows/records. It shows only the UNIQUE combinations. If you see six rows for the first date and program, that means it has six unique combinations with the fields taken in the table. Check the frequency  of the data as I have shown on the attahed qvw.

[surajap123]

Thanks you so much !!

Just one more query.. If i want to analyze the same data using various date fields like Month, year etc..what could be the expression??

As you can see i have master calender in the app. so could you help me with the expressions for month and year based analysis..

[tresesco]

Add the dimensions Year, Month...accordingly, the expression remain same and qlikview aggregates data as per dimension.