Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi gopinathanl,
could you please clarify, what do you mean with "back end"?
Regards,
Stefan
Back end means Script part,
in script level,
i done like
if(peek(Type)=Type,peek(Cumm)+Value,Value) as Cumm
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
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)
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
Have you tried the full accumaltion option on your table see attached
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