topic Re: Opening amount in QlikView

Opening amount

Fri, 24 May 2013 13:23:44 GMT

Hi All.

I’m hoping you can assist with this basic formula:

I am trying to total up transactions (AMOUNT) up and until (but not including) the month selected.

So, if I select 2013 (YEAR), May (MONTH), I would like the column to show the total of AMOUNT from the beginning of time until the end of April 2013.

Any ideas?
Thanks again

jvitantonio — Fri, 24 May 2013 13:29:18 GMT

Let's suppose you have a date filed like "MMM-YYYY" (ex: Jan-2013)

Now your formula should look like:

SUM({$<Date = {"<$(=getcurrentselections(Date))"}>} Amount)

Gysbert_Wassenaar — Fri, 24 May 2013 13:29:50 GMT

You're going to need a year-month field if you don't have a date field already. If you have a date field you can use this:

sum({<YEAR=,MONTH=,MyDate={'<$(=monthstart(max(MyDate)))'}>}AMOUNT)

Fri, 24 May 2013 13:46:49 GMT

Thanks for the prompt reply Gysbert

I'm working with pieta1230 on this, and have tried your formula above.

I have a PERIODKEY field which I bring through as YEAR & num(MONTH,'00') as PERIODKEY.

When i use your formula, i replace 'MyDate' with 'PERIODKEY' and the result of the selection:

YEAR=2013
MONTH=FEB

brings through all data until the end of 2012, but does not include transactions from Jan 2013..

Any ideas?

Gysbert_Wassenaar — Fri, 24 May 2013 13:53:29 GMT

Your PERIODKEY field should preferably be a number. YEAR*12+num(MONTH) or YEAR*100+Month would be better. Then this should work:

sum({<YEAR=,MONTH=,PERIODKEY={'<$(=max(PERIODKEY))'}>}AMOUNT)

But if PERIODKEY is a string than maxstring ought to work too, maybe just a bit slower.

Fri, 24 May 2013 13:58:44 GMT

Thanks Gysbert - that did the trick!!