CUmmulative sum

atudela — Thu, 23 Jun 2022 07:57:04 GMT

I want to represent the evolution of repairs done.

The data i have is the following: CampaignID, ProductID, ProdCampID, EntryDate and AcceptedDate.

CampaignID is the reparation campaign, depending on the reparation needed for each product it will be classified into a different Campaign.

ProductID is the ID of each particular product and this is unique

ProdCampID is the union of CampaignID and ProductID because although the ProductID is unique, a Product can simultaneously be in two or more reparation Campaigns.

EntryDate is the date in which the product entered the reparation Campaign.

AcceptedDate is the date in which the product was 'accepted' as okay due to a successful reparation

I have created a CanonicalDate that includes both entry and accepted dates and now I would like to create a graph with the evolution of how many Products entered a reparation campaing as follows:

In this graph

blue bar: the total number of units that have entered the campaign (this is the cumulative sum of the units)

red bar: of the total number of units that have entered the campaign, how many of these have been accepted (also cummulative)

light green bar: the number of units that have entered a campaign that particular day

yellow bar: the number of units that are in campaign and have not been repaired yet, that are pending

dark green bar: the number of products that have been accepted that particular day

I came up with the following way of representing this: (the DateType variable was created during the Canonical date creation in order to be able to distinguish between entry and accepted dates)

blue bar( entered units-cummulative): count(distinct{<DateType={'Entry'}>} ProdCampID )

red bar (accepted units-cummulative ) : count(distinct{<DateType={'Accepted'}>} ProdCampID )

light green bar (entered units): count(distinct{<DateType={'Entry'}>} ProdCampID )

yellow bar (pending units): rangesum(above(total count( distinct{<DateType={'Entry'}>} ProdCampID ),0,RowNo(total)))-rangesum(above(total count(distinct{<DateType={'Accepted'}>} ProdCampID ),0,RowNo(total)))

dark green bar (accepted units): count(distinct{<DateType={'Accepted'} >} ProdCampID )

but unfortunately this has been done in the front end and so date selections have an effect on the sums calculated and do not give a real cumulative answer. I checked the read only option, but doing this selections such as the Campaign cannot be applied...

I would like to do this is the back end, in the script, but I am having issues with the cummulaive part and I am not sure how to do this. Any help would be welcomed!

topic CUmmulative sum in Integration, Extension & APIs

CUmmulative sum