topic Re: use where in expression in QlikView

use where in expression

Fri, 28 Feb 2014 10:53:32 GMT

I have a voucher_date field and fiscal year date field

Voucher_date
01/jan/2012
01/feb/2012
01/Nov/2013
01/jan/2013
01/feb/2014

FISCAL_YEAR_DATE	OPENING_ BALNACE
01/jul/2011	1050000
01/Jul/2012	2460000
01/jul/2013	1500000
01/jul/2014	8500000

MY FISCAL YEAR START FROM JULY OF EVERY MONTH ABOVE TABLE SHOW THE OPENING BALANCE AT THE START OF

EACH FISCAL YEAR.

NOW I WANT TO SHOW THE SUM OF OPENING BALANCE IN TABLE

i.e if the user select the voucher_date let say 01/Jan/2014 which lies in fiscal year 2013-2014 so table must show

the opening balance of 2013-2014

reult

date	balnce
01/Jul/2013	15000000

i am using expression in table is ,

=SUM({$date(yearstart(voucher_date,0,7),'YYYY')=date(YEARSTART(FISCAL_YEAR_DATE,'YYYY'),OPENING_BALANCE)

I AM TRYING TO CONVERT BOTH DATE IN FISCAL YEAR '2013' THEN TRYING TO MATCH

ANY IDEA???

NOTE VOUCHER DATE IS IN VARIABLE.....

Re: use where in expression

Peter_Cammaert — Fri, 28 Feb 2014 22:47:58 GMT

=SUM({$date(yearstart(voucher_date,0,7),'YYYY')=date(YEARSTART(FISCAL_YEAR_DATE,'YYYY'),OPENING_BALANCE)

That's not a valid QlikView expression at all :

The left hand side of a set modifier should be a field name, nothing else
The right hand side should specify a set of values surrounded by curly braces.
Count or balance all punctuation marks. If one of them is missing, the expression will fall apart.

Read the help on Set Analysis in QlikView Desktop, especially the topic on Set Modifiers. It's a very good and concise summary.

For a working solution to your problem, check the document in attachment.

Peter

Re: use where in expression

Mon, 03 Mar 2014 10:25:02 GMT

OK ...

Thanks for advice actually I am short of knowledge about the usage of braces , dollar sign, etc in set analysis I will defiantly go through you advice.

Also thanks for the solution....it perfect