Expression result ignores a selection value

[jameswills]

Can anyone provide some assistance with an expression syntax please.

I have four dimensions:

GLPeriod.PeriodYearRP

GLPeriod.PeriodName RP

GLBal.ActualAmount

GLBal.YTDActual

I'm trying to construct a chart which reports the sum of the GLBal.ActualAmount where the GLPeriod.PeriodYearRP looks at the current selection minus 1 and the GLPeriod.PeriodNameRP is a constant value of P13 i.e. ignoring any selection made for that dimension.

I've been using

Sum({<GLPeriod.PeriodYearRP = {$(#=only(GLPeriod.PeriodYearRP)-1)}>}GLBal.ActualAmount)

to get the value for the year minus 1 but can't workout how to add the constant value of P13 for the GLPeriod.PeriodNameRP

Help?

[techvarun]

Try the below script

Sum({1<GLPeriod.PeriodYearRP = {$(#=only(GLPeriod.PeriodYearRP)-1), GLPeriod.PeriodNameRP= {'P13'}}>}GLBal.ActualAmount)

[jameswills]

Thanks Varun, tried the above but states error in expression?

[sunny_talwar]

May be this

Sum({<GLPeriod.PeriodYearRP = {"$(#=Only({<GLPeriod.PeriodYearRP = {'P13'}>}GLPeriod.PeriodYearRP)-1)"}>} GLBal.ActualAmount)

[jameswills]

o Thanks Varun, tried the above but states error in expression?

<https://community.qlik.com/servlet/JiveServlet/showImage/2-1328153-173875/pastedImage_0.png>

Cofion/Regards

James

Bwrdd Iechyd Prifysgol Aneurin Bevan/Aneurin Bevan University Health Board

• 01495 765459

• james.wills@wales.nhs.uk<mailto:james.wills@wales.nhs.uk>

Room 379, C Block South, Mamhilad House, Mamhilad Park Estate NP4 0YP

• http://FinanceBusinessSystemsHomepage<http://howis.wales.nhs.uk/sitesplus/866/page/47713>

P Helpwch arbed papur - oes angen i chi printio'r e-bost yma?Help save paper - do you need to print this e-mail?

PLEASE NOTE: The information contained in this message is intended for the named recipients only. It may contain privileged and confidential information and if you are not the addressee or the person responsible for delivering this to the addressee, you may not copy, distribute or take action in reliance on it. If you have received this message in error, please notify me immediately by return e-mail.

Bwriedir yr wybodaeth yn y neges hon ar gyfer y derbyniwr/wyr a enwyd yn unig. Gall gynnwys gwybodaeth freintiedig a chyfrinachol ac os nad chi yw'r cyfeiriedig na'r sawl sy'n gyfrifol am gyflwyno'r neges i'r cyfeiriedig, ni chewch gopio na rhannu'r neges na gweithredu yn ei chylch. Os ydych wedi derbyn y neges hon drwy gamsyniad, a wnewch chi roi gwybod imi ar unwaith drwy e-bost.

It should be noted that the contents of this e-mail may be subject to public disclosure under the Freedom of Information Act 2000. Therefore, the confidentiality of this message and any reply cannot be guaranteed.

Dylid nodi y gellir datgelu cynnwys yr e-bost hwn i'r cyhoedd o dan y Ddeddf Rhyddid Gwybodaeth 2000. Felly, ni ellir gwarantu cyfrinachedd y neges nac unrhyw ymateb.

[#6]

I think there is just one character missing in Varun's Statement. I would try it like written down below

sum({1<GLPeriod.PeriodYearRP = {$(#=only(GLPeriod.PeriodYearRP)-1)}, GLPeriod.PeriodNameRP = {'P13'} >} GLBal.ActualAmount)

[jameswills]

Thanks for the reply Franziska. I've tried your expression but unfortunately it still returns a zero value when there is definitely data :-(.

Pulling my hair out with this one at the mo, I'll be bald soon 😞

[Kushal_Chawda]

try this

Sum({<GLPeriod.PeriodYearRP = {"$(=max({<GLPeriod.PeriodYearRP = {'P13'}>}GLPeriod.PeriodYearRP)-1)"}>} GLBal.ActualAmount)

[jameswills]

Thanks Kushal, this expression does return a value which is great but it's not ignoring the GLPeriod.PeriodMonth selection. It's returning a cumulative value for the GLBal.ActualAmount based on the periods selected in the LOV.

What I'm after is an expression which returns the GLPeriod.PeriodYear value - 1, but always returns the figure for the GLBal as at GLPeriod.PeriodMonth P13, irrespective of the period selected in GLPeriod.PeriodMonth. I'm essentially trying to analyse this years expenditure against a baseline of last years YTD?