topic Re: Getting YTD values (set analysis) in QlikView

Getting YTD values (set analysis)

Thu, 23 May 2013 18:50:18 GMT

I am trying to get YTD SalesAmount values to show, but do not - the measure just dissapears from screen - do not get an error

My YTD SalesAmount formula is:

Sum({$<MyPeriodID= {"<=$(=Max(MyPeriodID))"}, MyYear= {"$(=Max(MyYear))"}, MyQuarter = , MyMonthNum = , > } SalesAmount)

MyPeriodID was loaded as (MyYear*12) + MyMonthNum, giving me a sequential value that I can use for period comparisons.

Not sure if I am missing something. I do not show MyPeriodID on the screen for the user.

Re: Getting YTD values (set analysis)

Gysbert_Wassenaar — Thu, 23 May 2013 18:57:35 GMT

Looks ok to me. The only thing that springs to mind is that you could also have a date field. If so perhaps that needs to be added to the set modifier. If that's not the problem you'll have to create a sample document that exhibits the problem your experiencing and upload that file here so we can have a look at it.

Re: Getting YTD values (set analysis)

Thu, 23 May 2013 19:02:03 GMT

So, there is no date field, as this is period snapshot data.

I will create a small sample and upload if I cannot figure this out.

Re: Getting YTD values (set analysis)

rwunderlich — Thu, 23 May 2013 23:57:59 GMT

There should be no comma after

MyMonthNum = ,

-Rob

http://robwunderlich.com

Re: Getting YTD values (set analysis)

Fri, 24 May 2013 03:12:02 GMT

Yes - I saw that after the initial post. Removed it, but did make a difference.

I am wondering if it has someting to do with the section:

MyPeriodID= {"<=$(=Max(MyPeriodID))"}

MyPeriodID is linked one to one with MyMonth - I.E. Each MyMonth has a MyPeriodID

Also, MyPeriodID is not dispayed anywhere, so no possibility for a user to select it, so will Max(MyPeriodID) even get evaluated?

Re: Getting YTD values (set analysis)

Fri, 24 May 2013 04:14:51 GMT

I had extra space in [Fiscal Year_H ] and later referenced as [Fiscal Year_H]. So that caused nothing to show.

So, that corrected, both if these (1 and 2) below work. The first, I personally would consider a true YTD, as you can select any month or quarter, and the calc does not change. The second YTD , is YTD based on the greatest month selected, because of the inclusion of ... Max(Fiscal_MonthID_H). I think the second would be misleading to the user.

I think its safer to use the first, (completely ignoring the month and quarter), to show a true YTD. What do you think?

Sum({$< [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])

Sum({$< Fiscal_MonthID_H = {"<=$(=Max(Fiscal_MonthID_H))"

}, [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])

Re: Getting YTD values (set analysis)

Fri, 24 May 2013 04:15:54 GMT

I had extra space in [Fiscal Year_H ] and later referenced as [Fiscal Year_H]. So that caused nothing to show.

I think its safer to use the first, (completely ignoring the month and quarter), to show a true YTD. What do you think?

Sum({$< [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])

Sum({$< Fiscal_MonthID_H = {"<=$(=Max(Fiscal_MonthID_H))"

}, [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])

Re: Getting YTD values (set analysis)

rwunderlich — Fri, 24 May 2013 18:32:43 GMT

It depends on what want to show, and how much control you want to give to the user. I've seen users request it both ways. The more insidious problem is what if I select Product "Bicycles", and I haven't sold any Bicycles yet this year? YTD should be 0, right? I believe your expression will return previous year values.

I've struggled with getting this just right in Qlikview Components (http://qlikviewcomponents.org) Calendars. The latest version was adjusted to honor only Calendar field selections in calculating periods. This seems to be what most people want. So works like your expression #2 above, but guards against the no Bicycles case.

-Rob

Re: Getting YTD values (set analysis)

Fri, 24 May 2013 18:47:44 GMT

Thanks for the insight. I would have thought the no bikes scenario would have returned 0, for the current year (if current year is selected), and not jumped back to the previous Max().