Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

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

1 Solution

Accepted Solutions
MVP
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
robert99
Valued Contributor II

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

CallVDatesitePartcount  (1)£ see (2 )belowsum(CallExpense)
10001/01/2012AAAC102812.5012.50
10001/01/2012AAAL102812.5012.50
10001/01/2012AAAp102812.5012.50
10101/01/2012AAAC103812.5012.50
10101/01/2012AAAp103812.5012.50
10201/01/2012AAAC104812.5012.50
10201/01/2012AAAL104812.5012.50
10201/01/2012AAAp104812.5012.50
10301/02/2012BBBC105333.3333.33
10301/02/2012BBBL105333.3333.33
10301/02/2012BBBp105333.3333.33
10401/03/2012BBBC106333.3333.33
10401/03/2012BBBL106333.3333.33
10401/03/2012BBBp106333.3333.33
10501/02/2012CCCC102425.0025.00
10501/02/2012CCCC107425.0025.00
10501/02/2012CCCL107425.0025.00
10501/02/2012CCCp107425.0025.00
Total


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

MVP
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

robert99
Valued Contributor II

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

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

CallVDatesitePartsum(CallExpense)sweuhlsweuhl Adj {1}




37.50200.0037.50
10001/01/2012AAAC10212.50100.0012.50
10501/02/2012CCCC10225.00100.0025.00