# New to QlikView

Discussion board where members can get started with QlikView.

Valued Contributor II

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

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.

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

1 Solution

Accepted Solutions
MVP

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

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

3 Replies
Valued Contributor II

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

 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 Part) 2  Call expense calculation before download  into Excel £100  /   (count(total Part))

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

MVP

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

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

Valued Contributor II

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

Stefan

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

=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

Community Browser