topic Sum UPTO Selected month in QlikView

Sum UPTO Selected month

disqr_rm — Tue, 09 Jun 2009 13:13:00 GMT

Hi Guys,

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.

Thanks for your help.

Sum UPTO Selected month

disqr_rm — Tue, 09 Jun 2009 20:52:48 GMT

Hi Guys, here is an example document for what I need to do. Hopefully now I get an idea from expertes out there.

Thanks

Sum UPTO Selected month

johnw — Thu, 11 Jun 2009 02:28:56 GMT

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.

Sum UPTO Selected month

Michael_Reese — Thu, 11 Jun 2009 02:50:10 GMT

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)