topic Re: Aggregating data in QlikView

Aggregating data

surajap123 — Thu, 08 May 2014 10:21:37 GMT

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.

Re: Aggregating data

tresB — Thu, 08 May 2014 10:24:00 GMT

Straight table

Dim1: ProgName

Dim2: Date

Exp1: Sum(Hrs)

Exp2: Sum(TotalHours)

Re: Aggregating data

its_anandrjs — Thu, 08 May 2014 10:29:29 GMT

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|

Re: Aggregating data

surajap123 — Thu, 08 May 2014 11:16:27 GMT

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.

Re: Re: Aggregating data

tresB — Thu, 08 May 2014 11:25:02 GMT

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.

Re: Aggregating data

surajap123 — Thu, 08 May 2014 11:31:27 GMT

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

Re: Re: Aggregating data

tresB — Thu, 08 May 2014 11:39:04 GMT

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