topic Problems with YTD in QlikView

Problems with YTD

Fri, 25 Jun 2010 14:21:33 GMT

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

201002

201003

201004

113

201005

141

201006

176

201007

204

201008

232

201009

267

201010

295

201011

323

201012

365

Thanks in advance

Problems with YTD

Fri, 25 Jun 2010 15:52:07 GMT

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)"

Problems with YTD

nathanfurby — Fri, 25 Jun 2010 16:13:21 GMT

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)

Problems with YTD

nathanfurby — Fri, 25 Jun 2010 16:16:04 GMT

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

Problems with YTD

Fri, 25 Jun 2010 16:20:06 GMT

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.

Problems with YTD

nathanfurby — Fri, 25 Jun 2010 16:25:38 GMT

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

Problems with YTD

Fri, 25 Jun 2010 16:26:40 GMT

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 !

Problems with YTD

nathanfurby — Fri, 25 Jun 2010 16:31:49 GMT

Did you look at the last qvw file I posted?

Problems with YTD

Fri, 25 Jun 2010 16:41:44 GMT

Thanks Nathan and carney.

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

Cheers

Problems with YTD

nathanfurby — Fri, 25 Jun 2010 16:50:40 GMT

No worries - have a nice quiet weekend