Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Straight table
Dim1: ProgName
Dim2: Date
Exp1: Sum(Hrs)
Exp2: Sum(TotalHours)
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|
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.
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.
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..
Add the dimensions Year, Month...accordingly, the expression remain same and qlikview aggregates data as per dimension.