8 Replies Latest reply: May 24, 2013 2:47 PM by David Dumas RSS

    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.

        • Re: Getting YTD values (set analysis)
          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.

            • Re: Getting YTD values (set analysis)

              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)

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

              • Re: Getting YTD values (set analysis)
                Rob Wunderlich

                There should be no comma after

                MyMonthNum = ,

                 

                -Rob

                http://robwunderlich.com

                  • Re: Getting YTD values (set analysis)

                    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)

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

                        • Re: Getting YTD values (set analysis)
                          Rob Wunderlich

                          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