Summing to a Minimum Selection

[#1]

Hi,

I currently have an opening balance expressiom that is calculated from a PeriodIndex field, (Numbers 1-180 representing months).

The opening balance figure is calculated from adding up all periods below the the minimum PeriodIndex selected.

It is scripted as:

Sum({<PeriodIndex={"<$(=Min(PeriodIndex))"},.................................>} Amount)

However I am now building my dashboard with Fiscal Years and Periods and therefore I want to change my expression from Period Index to FY and Periods. There is a slight problem also that my FY and Periods fields do not stretch back as far as PeriodIndex and am wondering is that perhaps a problem when building my expression.

Can anyone offer any advice for how to script my expression correctly to sum everything up to my minimum year selected and minimum period selected going back as far as my PeriodIndex.

Any help would be greatly appreciate and would help prevent me from losing my mind!!!!

Thanks,

James

[effinty2112]

Hi James,

If I understand what you say then the problem will be resolved if you add a financial year field to your calendar. Try something like this in your calendar script:

if (Ceil(Month(Date) / 3)=1,

Year(Date)-1 & '/' &  Year(Date),

Year(Date) & '/' & Text(Year(Date)+1) )

as [FinYear]

I'm assuming that your financial year will run from 1st Apr.

Hope this helps

[#3]

Hi Andrew,

Thanks for your reply, really appreciate the help.

i have already loaded in FY and Periods in my master calendar. So they exist as fields in my dashboard but when I try to change my expression away from PeriodIndex to an FY and Period based one I cannot get it to work.

In an ideal world i would just code in:

Sum({<FY={"<$(=Min(FY))"},{<Period={"<$(=Min(Period))"},..........Amount)

but obviously this wont work as an expression without an error. Also as my PeriodIndex goes further back than my calendar figures for FY and Period I am wondering will it be an issue performing the summing everything less than function.

thanks,

James

[effinty2112]

Hi James,

Do you really need a Period field as well as a PeriodIndex field? Aren't both numerical indexes for calendar months? Unless your financial years start from 6th April or another date that is not a MonthStart any numerical index for a month will serve our purpose.

Of course it could be that I've missed something. It wouldn't be the first time.

[#5]

Thanks Andrew,

Sorry about how difficult this is to explain and unfortunately I am not able to upload a qvw of my problem due to confidentiality issues and my desire to not be imprisoned !!

Re your last post:

i am trying to phase out PeriodIndex alright to an FY and Periods Calendar. Right now they are linked, i.e when I select a period it matches up with the corresponding PeriodIndex.

However when my expression for my opening balance figure is based on PeriodIndex and i select a from the field of PeriodIndex I get correct values.

But when I select a FY and a Period I obtain values that are not correct.

So when working correctly it resembles this:

And when not working correctly it resembles this:

Hopefully these Images better explain my problem. Also to remind my current expression is:

Sum({<PeriodIndex={"<$(=Min(PeriodIndex))"},.................................>} Amount)

Thanks again Andrew!

[effinty2112]

OK - Period is  1 - 12 running over the FY.

So when you select PeriodIndex 179 the only possible values for FY and Period are 2016 and 2 respectively.

When you clear PeriodIndex and select FY - 2016 and Period - 2, what are the possible values of PeriodIndex?

[#7]

Only possible value is 179.