cumulative sum

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have some data as follows ( there are more like 20 different impacted business and Delivery there are many more options too)

Project Number Project Hours Delivery Impacted business

PRJ001 20 Q1 2016 Delivered EQUITY MKTS

PRJ002 50 Overdue RATES

PRJ003 10 On Hold EQUITY MKTS

PRJ004 30 Q2 2016 Delivered EQUITY MKTS

PRJ005 10 Q2 2017 Planned RATES

PRJ006 5 Q4 2016 Planned EQUITY MKTS

PRJ007 90 Q2 2015 Delivered EQUITY MKTS

I have built the following pivot table where the idea is to show the count of projects, the number of hours the account for, the cumulative hours as you go further right, and the quarterly target (this is picked up from another table which has Impacted business and target in)

I built this pivot table with 2 Dimensions: Impacted Business and " =if(WildMatch(Delivery,'Skipped', 'On Hold','Overdue','*$(=vCurrentYear)*'),Delivery)" as I only want to deliveries for this year or projects which are on hold/overdue/skipped.

It has expressions:

1. Number of projects =count(distinct ([Project Number]))

2. Hours =num(sum(YearlyHours),'##.')

3. Cumulative Hours - This is the part I need help with

4. Quarterly Target:

(

WildMatch(Delivery,'Skipped','Overdue','On Hold'), Null(),

(Delivery like 'Q1 *',[2016 Target]/4,

(Delivery like 'Q2 *',[2016 Target]/2,

(Delivery like 'Q3 *',[2016 Target]*0.75,

(Delivery like 'Q4 *',[2016 Target],

)

))

)

For cumulative hours the code I have at the moment is as follows. You can see from the diagram that hours and cumulative hours at the moment are the same. I would like to get it so that Q2 2016 delivery cumulative hours is the sum of Q1 2016 delivered + Q2 2016 delivered. I have played around with aggr and range sum (which is what my last 2 rows are for that are not calculating!) but i cant seem to get it to calculate as i need it. I understand why its doing what its doing... i guess its taking the columns as a type of filter... but how can i bypass this?

if
(
WildMatch(Delivery,'Skipped','Overdue','On Hold'), Null(),
  if
  (Delivery='Q1 ' & $(=vCurrentYear) & ' Delivered',sum({<Delivery ={$(=vQ1Delivered)}>}YearlyHours)
,
  if
  (Delivery='Q1 ' & $(=vCurrentYear) & ' Planned',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned)}>}YearlyHours)
,
  if
  (Delivery='Q2 ' & $(=vCurrentYear) & ' Delivered',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered)}>}YearlyHours)
  ,
if
  (Delivery='Q2 ' & $(=vCurrentYear) & ' Planned',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned)}>}YearlyHours)
,
if
  (Delivery='Q3 ' & $(=vCurrentYear) & ' Delivered',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned),$(=vQ3Delivered)}>}YearlyHours)
,
if
  (Delivery='Q3 ' & $(=vCurrentYear) & ' Planned',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned),$(=vQ3Delivered),$(=vQ3Planned)}>}YearlyHours)
,
if
  (Delivery='Q4 ' & $(=vCurrentYear) & ' Delivered',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned),$(=vQ3Delivered),$(=vQ3Planned),$(=vQ4Delivered)}>}YearlyHours)
,
if
  (Delivery='Q4 ' & $(=vCurrentYear) & ' Planned',sum({<Delivery ={$(=vQ1Delivered),$(=vQ1Planned),$(=vQ2Delivered),$(=vQ2Planned),$(=vQ3Delivered),$(=vQ3Planned),$(=vQ4Delivered),$(=vQ4Planned)}>}YearlyHours)


  ) ) ) ) ) ) ) ))

Thanks,

Lizi

Re: cumulative sum

sunny_talwar — Thu, 26 May 2016 17:14:22 GMT

Would it be possible to share a sample? It would be easier and faster to help you with a sample.

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: cumulative sum

Fri, 27 May 2016 08:03:03 GMT

Sorry, yes, I have attached a sample to original post (sorry I cant share my real application due to sensitive info)

Cumulative hours row should show null for on hold, overdue or skipped columns, but for Q1/Q2/Q3/Q4 it should accumulate 'hours' as you go right. like so:

stalwar1‌

Re: cumulative sum

Tue, 31 May 2016 10:21:07 GMT

I have found my solution - using 'TOTAL' in set analysis: https://community.qlikview.com/thread/14070

Thanks to any one who took a look!

topic Re: cumulative sum in QlikView

cumulative sum

Re: cumulative sum

Re: cumulative sum

Re: cumulative sum