Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

help needed - Cummulative

dear all,

i have transaction table,the data looks like

Type      Month     Amt

A           Feb         10

A           Mar         20

A           Apr         10

B          Jan          20

B          Feb          10

C          Jan          30

C          Feb          20

C          Mar          10

if the user selects the month Apr, then i need to show the output as

Type      Month    Cumm_Amt

A           Apr         40

B           Apr         30

C           Apr         60

its working fine if i use the set analysis in the chart.

but wen i do this one in back end, i m getting only Type A for the month of Apr.

reason is i dont have the transation for B and C in Apr.

could anyone help me how to resolve this one.

7 Replies
swuehl
MVP
MVP

Hi gopinathanl,

could you please clarify, what do you mean with "back end"?

Regards,

Stefan

Not applicable
Author

Back end means Script part,

in script level,

i done like

if(peek(Type)=Type,peek(Cumm)+Value,Value) as Cumm

swuehl
MVP
MVP

Hi gopinathanl,

as a note, you might need to sort your data first after Type, Month to ensure that Type data is consecutive (probably you have already done this).

So, as I understand you want either

- add a row to the data for each missing month of a type (but up to which month?) with value 0 and correct accumulation. Then you have all data needed for selection.

or

- include data in your table which is not selected (selected Month= April, but you want to show the last existing month for each type in row Cumm_Amt).

At the moment, I think about using set expression again, but this is probably not what you want.

What ist the requirement behind your question? To not use set expression as a exercise? Or do you have performance problems if using large amount of data and you want to perform the needed calculation while loading data?

Regards,

Stefan

Not applicable
Author

hi stefan,

thanks for reply...

i want to add a row for each missing month upto today.

bcoz wen i do the Cummulative in back end, some of them not having the transaction after certain months.

so the cummlative is computed upto that month. wen i select the current month it shows null.bcoz its not having the tranaction for last two or three months.it may have next month (we cant decide).

i done it in set analysis but my chart going very slow(bcoz of large amount of data)

swuehl
MVP
MVP

Hi gopinathanl,

please find my proposal for adding the missing rows attached.

As you can see in the script, these are the steps I used:

1. Create or load the original input data table, plus additional unique TypeMonth

2. Create Table with preset values 0 for all Types, all Months

3. add rows with zero value to original data for all missing Type / Months combinations (i.e. TypeMonth)

4. Do the actual accumulation (your existing code, I added an order by)

5. add calendar till today's Month (lookup Month# to Month name)

I am just curious, how many rows (Types / Months) do you load, resulting in bad performance using set analysis?

I am not sure if my  code is performant, at least it is calculated at load time.

Regards,

Stefan

shaunsomai
Contributor
Contributor

Have you tried the full accumaltion option on your table see attached

swuehl
MVP
MVP

Hi Shaunsomai,

yes I have tried, but this gives the accumulated values of the column, but as I gopinathanl understand want it per Type and only up to selected Month.

As I also understand gopinathanl was able to get the result using set expression in chart, but explicitely wanted to put the calculcations into the backend.

Regards,

Stefan