I am sure this has been asked many times before, but couldn't find with my search.
How do I show sum of amount upto the selected month in the timeline?
I have a fact table with Amount and Date. Then Date is connected to TimeLine which has month, year and monthname. I would like to show in the chart a dimention from fact and sum of amount upto selected month. If no month is selected, it should show sum of all the months.
For example, if user selects March 2009, I would like to should in the expression the sum of all the AMOUNTs from earliest possible date until end of March 2009.
One approach is to do the accumulation in the script:
LEFT JOIN LOAD ID ,if(GL=previous(GL),peek('ACCUM')+AMOUNT,AMOUNT) as ACCUM RESIDENT Fact ORDER BY GL,DATE,ID;
Then use max(ACCUM) as your expression in the table. Now, that only works if all of your values are positive. If you have negative values, let me know, and I'll try to come up with something else.
You could create a date table that is detached fom the data model. Use those dates as your filters. When it is selected, your expression can evaluate it.
Something like this: sum(If(DataDate <= DateSelected, amount))
The DateSelected variable reads the selected date and returns the max() selected Date from the detached Date table if they are selecting month. This way, the expresion is only evaluating one value and not a range. DateSelected = max(DetachedDate)
