topic Re: help needed - Cummulative in QlikView

help needed - Cummulative

Fri, 08 Jul 2011 08:49:13 GMT

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.

help needed - Cummulative

swuehl — Fri, 08 Jul 2011 10:14:30 GMT

Hi gopinathanl,

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

Regards,

Stefan

Re: help needed - Cummulative

Fri, 08 Jul 2011 10:19:44 GMT

Back end means Script part,

in script level,

i done like

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

Re: help needed - Cummulative

swuehl — Fri, 08 Jul 2011 11:47:13 GMT

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.

- 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

Re: help needed - Cummulative

Mon, 11 Jul 2011 04:53:16 GMT

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)

Re: help needed - Cummulative

swuehl — Tue, 12 Jul 2011 15:20:34 GMT

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

Re: help needed - Cummulative

shaunsomai — Tue, 12 Jul 2011 19:49:07 GMT

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

help needed - Cummulative

swuehl — Wed, 13 Jul 2011 09:03:47 GMT

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