Cumulation Month and Year in the Script

[abonnery]

Dear Qlik users, 

I would like to create a table of cumulative values in the script.

Contrary to all the post of this subject in the community, I don't have a specific fields of dates but rather 2 fields : the month and the year in the script.

I have tried a few different ways to design such a tables without any success. 

As you can see, the value field is EAC.

I am consolidating the values per month and per year in one sheet.

Below, the EAC21 field is defined like such

sum({$<[Cost Baseline Version]={"RF*"},[Monthly Cost Destination]={'CAPEX'}>} EAC)/1000000

and I want to edit the script so that the very same definition would do "Cumulated EAC21"  :

I am quite lost and I really have no idea as of what to try next.

Thank you very much for your help !

[rubenmarin]

Hi, you can do a load  order by year and month, and use pek to check where the year changes and start again.

First note that if you have many diffretn rows for each year-month you will need and extra table to store accumulations, in any case an script like this would do the accumulation in script:

tmpInitialData:
//Load excel data

FinalTable:
LOAD
  Year &'_'& Month as AccumKey, // Only if you neeed and additional table to store accumulation, this woud be the key to realte to the main table
  Year,
  EAC,
  IF(peek(Year)=Year
    ,RangeSum(EAC, Peek(Acum_EAC))
    ,EAC)  as Acum_EAC
Resident tmpInitialData
Where [Cost Baseline Version] like "RF* AND Monthly... //Only if needed
Order By Year, Month;

[abonnery]

Hello, 

Thank you for your answer. 

I will be concatenating this table with another so I need to keep this exact same fields and will be modifying only the Cumulated Values. 

So I inspired from your script (The order by was not recognized in the data load editor), I have enclosed the one I loaded in Qlik Sense below. I need to keep both Year and Month fields as well as the other taggs (customer, etc). 

The cumulation didn't work. It shows normal non cumulated values. I believe this is due to the 1st condition of the if statement.

Do you know how I can modify this script so that it works ?

Thank you for your help.

Cum_Table:


LOAD
    '<NoData>' AS "Date of forecast_C" ,
     "Project Name",
    "Cost PLW Curve Name" AS "Cost PLW Curve Name_C",
    "Customer Base entity" AS "Customer Base entity_C",
    
    "Customer Name" AS  "Customer Name_C",
    
    "Year" AS "Year_C",
    "Month" AS "Month_C",
    'Cumulated_'&"Cost Baseline Version" AS "Cost Baseline Version_C",
    "Cost Origin" AS "Cost Origin_C",
    "Monthly Cost Destination" AS "Monthly Cost Destination_C",
    "Monthly Cost Origin" AS "Monthly Cost Origin_C",
     IF(peek(Year)=Year,RangeSum(EAC, Peek("Month"&"-"&"Year")),EAC)  as Acum_EAC
     
    FROM [lib://Tables for Qliksense.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Cost);

[rubenmarin]

Hi, if you rename Year as Year_C, when you access the rpevious row it is stored as Year_C, no Year, so Peek() needs to query for this field name:

IF(peek(Year_C)=Year,RangeSum(EAC, Peek("Month"&"-"&"Year")),EAC)  as Acum_EAC

[abonnery]

Hello, 

I changed it (script below) and it's still not working. The cumulation doesn't work and the app tells me 'internal error' every 2 seconds.

The 3rd column is the Acum field from this script and the 4th is what I would like it to be.

Cumulated_Table:


LOAD
    '<NoData>' AS "Date of forecast_C" ,
     "Project Name",
    "Cost PLW Curve Name" AS "Cost PLW Curve Name_C",
    "Customer Base entity" AS "Customer Base entity_C",
    
    "Customer Name" AS  "Customer Name_C",
    
    "Year" AS "Year_C",
    "Month" AS "Month_C",
    'Cumulated_'&"Cost Baseline Version" AS "Cost Baseline Version_C",
    "Cost Origin" AS "Cost Origin_C",
    "Monthly Cost Destination" AS "Monthly Cost Destination_C",
    "Monthly Cost Origin" AS "Monthly Cost Origin_C",
     IF(peek(Year_C)=Year,RangeSum(EAC, Peek("Month"&'-'&"Year")),EAC) as Acum_EAC 
     
    FROM [lib://Tables for Qliksense.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Cost);

[rubenmarin]

Hi, I'm my initial post I used an initial table to laod excel file, and then another table to load using order by, this code it's not doing that, if excel is sorted it may work but I would do the double step to ensure the load order of the rows.

Also I don't understan this sentence: IF(peek(Year)=Year,RangeSum(EAC, Peek("Month"&"-"&"Year")),EAC) as Acum_EAC

Mine was: IF(peek(Year)=Year,RangeSum(EAC, Peek(Acum_EAC)),EAC) as Acum_EAC

[abonnery]

Hello

I have tried the line :

IF(peek(Year)=Year,RangeSum(EAC, Peek(Acum_EAC)),EAC) as Acum_EAC

and the cumulation didn't work (values not cumulated)

as well as the statement :

IF(peek("Year_C")=Year,RangeSum(EAC, Peek(Acum_EAC)),EAC) as Acum_EAC

This time, some sort of cumulation happened but not the right one. On the left the cumulation from the script and on the right the non-cumulated values and the right cumulation (from the Accumulation of the table not from the script).

Do you know why the accumation from the script is not the same as the good one ? Why doesn't it start at the first value of the year (January 2021) ?

Thank you for your help.

[rubenmarin]

Hi, Yes, if renamed it should be Peek('Year_C').

It the value it's a lot higher maybe it's caused because there are more than one row for each Year-Month and the expression is doing a Sum() of different accumulated values.

what the field Acum_AEC does is: if the row previously loaded has the same year than this one, add the row AEC the the value already accumualdted in Acum_AEC; and this Acum_AEC will be used in the next row if still is in the same year.

You can add a RowNo() as NoOfRow field in script, and add it to the table to check what is happening row by row.