Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Opening amount

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Opening amount

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.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Luminary
Luminary

Re: Opening amount

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)

MVP & Luminary
MVP & Luminary

Re: Opening amount

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)


talk is cheap, supply exceeds demand
Not applicable

Re: Opening amount

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?

MVP & Luminary
MVP & Luminary

Re: Opening amount

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.


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

Re: Opening amount

Thanks Gysbert - that did the trick!!