Expression / (count(total<site,VDate> Part)) / drill down issues etc

[robert99]

I’ve set up a simple example to explain an issue I have at work.

The business is a service company. The company incurs a callout cost for each distinct site and date visit. So if two calls (phone request to repair equipment) are attended in one visit only one callout expense are incurred.

I need to enter the cost (not revenue this is already in the system) in QlikView.

I can easily do this by downloading into Excel and then uploading. But I have tried for days (off andf on)  to do the calculation completely in QlikView (without the download and upload) without success so far. (I'm possible missing the obvious)

The fields in the example are

• Call (unique call number when call is received)
• VDate (Site visit date to do the repair)
• Site (equipment location)
• Parts (parts used)

Assume a cost of £100 (for simplicity) per site visit

The method I used was as follows. Calculate costs in QV per call, vdate, site and part and then download into excel and upload as CallExpense (refer table below)

This works well. But I can not do this all in QV (as an expression preferably not script and expression) especially when filtering by various fields

Thanks for any help

[robert99]

Call	VDate	site	Part	count  (1)	£ see (2 )below	sum(CallExpense)
100	01/01/2012	AAA	C102	8	12.50	12.50
100	01/01/2012	AAA	L102	8	12.50	12.50
100	01/01/2012	AAA	p102	8	12.50	12.50
101	01/01/2012	AAA	C103	8	12.50	12.50
101	01/01/2012	AAA	p103	8	12.50	12.50
102	01/01/2012	AAA	C104	8	12.50	12.50
102	01/01/2012	AAA	L104	8	12.50	12.50
102	01/01/2012	AAA	p104	8	12.50	12.50
103	01/02/2012	BBB	C105	3	33.33	33.33
103	01/02/2012	BBB	L105	3	33.33	33.33
103	01/02/2012	BBB	p105	3	33.33	33.33
104	01/03/2012	BBB	C106	3	33.33	33.33
104	01/03/2012	BBB	L106	3	33.33	33.33
104	01/03/2012	BBB	p106	3	33.33	33.33
105	01/02/2012	CCC	C102	4	25.00	25.00
105	01/02/2012	CCC	C107	4	25.00	25.00
105	01/02/2012	CCC	L107	4	25.00	25.00
105	01/02/2012	CCC	p107	4	25.00	25.00
Total				18	5.56	400.00

1   Count Calculation

count  ( total<VDate, site> Part)

2  Call expense calculation before download  into Excel

£100  /   (count(total<VDate,site> Part))

Sum CallExpense is the sum from an Excel download and upload of the CallExpense field (from calculation 2 above).

[swuehl · Accepted solution]

Either use a total mode 'sum of rows' for your expression (option on expression tab for a straight table),

or use the equivalent expression using advanced aggregation for total mode expression total in a pivot table or text box:

=sum( aggr(

100  /   (count(total<VDate,site> Part))

, Call, VDate, site, Part))

Hope this helps,

Stefan

[robert99]

Stefan

Brilliant. And thanks for your prompt reply.

I still do not fully understand aggr but will work on this next weekend.

I had to adjust the above formula by adding {1} though

=sum( aggr( 100  /   (count({1} total<VDate,site> Part))

, Call, VDate, site, Part))

This was to allow drill down as shown in the extract below (drill down by C102)

I will try this on the full database tomorrow.

Thanks again RJ

Call	VDate	site	Part	sum(CallExpense)	sweuhl	sweuhl Adj {1}
				37.50	200.00	37.50
100	01/01/2012	AAA	C102	12.50	100.00	12.50
105	01/02/2012	CCC	C102	25.00	100.00	25.00