Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
1:
Sum({$< [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])
2:
Sum({$< Fiscal_MonthID_H = {"<=$(=Max(Fiscal_MonthID_H))"
}, [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])
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.
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.
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?
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?
1:
Sum({$< [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])
2:
Sum({$< Fiscal_MonthID_H = {"<=$(=Max(Fiscal_MonthID_H))"
}, [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])
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?
1:
Sum({$< [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])
2:
Sum({$< Fiscal_MonthID_H = {"<=$(=Max(Fiscal_MonthID_H))"
}, [Fiscal Year_H ]= {$(=Max([Fiscal Year_H ]))}, [Fiscal Quarter_H ] = , RA_Month_H => } [WORLD Billed Amt ])
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
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().