Problems with YTD

[#1]

Folks,

I am hoping someone can help me with this problem. I have attached a simple sample app. I am trying to calculate YTD and PY , and PY YTD values on a number of apps, but I think the internal company calendar is causing a problem.

I have read a number of postings regarding YTD calculations, and have found a number of scripts that I have tried, an example of which is used in the attached app.

In the attached app I have a text box which I am using to sum the number of days YTD. I am using a list box 'Metroline Yr-Pd' to pick the period, yet my calc only ever give the number of days in the period, whilst I would expect the number below, so for period 201004 I would expect 113.

Metroline Yr-Pd	YTD Days	Days
201001	22	22
201002	50	28
201003	85	35
201004	113	28
201005	141	28
201006	176	35
201007	204	28
201008	232	28
201009	267	35
201010	295	28
201011	323	28
201012	365	42

Thanks in advance

[#2 · Accepted solution]

Create two Variables:
v1Mth, v2Mth

Set v1Mth = 1 and v2Mth = [Met Pd]

Now in the text box use the set analysis formula

=sum({$<[Met Pd]={">=$(#v1Mth) <=$(#v2Mth)"}>} CurYTDFlag)

This will give you a value of 113 when you select period 4

v1Mth) <=$(#v2Mth)"

[nathanfurby]

Nice solution by Carney. Was just missing an extra bit in the Set Expression to nullify the selection on [Metroline Yr-Pd]:

=sum({$<[Metroline Yr-Pd]=,[Met Pd]={">=$(#v1Mth) <=$(#v2Mth)"}>} CurYTDFlag)

[nathanfurby]

Sorry Carney - just realised that what you meant was to use [Met Pd] in a List box for the selection.

[#5]

Hi carneyfm,

Thanks for your reply, I have tried this but don't seem to get the same result as you, I get 176 irrespective of what period I pick. See attached qvw.

[nathanfurby]

You must have done something wrong. Worked for me. See attached qvw.

[#7]

Thanks for your input Nathan, but I have tried using a list box for Met Pd, but I still don't get 113 when choosing 4, I must be missing something !

[nathanfurby]

Did you look at the last qvw file I posted?

[#9]

Thanks Nathan and carney.

My mistake I typed the second variable in incorrectly, it must be a Friday afternon!!

Cheers

[nathanfurby]

No worries - have a nice quiet weekend