Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)"
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)"
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)
Sorry Carney - just realised that what you meant was to use [Met Pd] in a List box for the selection.
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.
You must have done something wrong. Worked for me. See attached qvw.
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 !
Did you look at the last qvw file I posted?
Thanks Nathan and carney.
My mistake I typed the second variable in incorrectly, it must be a Friday afternon!!
Cheers
No worries - have a nice quiet weekend