Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Getting YTD values (set analysis)

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

8 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

There should be no comma after

MyMonthNum = ,

-Rob

http://robwunderlich.com

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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().