7 Replies Latest reply: Oct 25, 2016 11:59 AM by John Witherspoon RSS

    YTD figures based on Selection.

    Luc CELESTIN

      Hello

       

      I want to display the figures for a specific selection on Year and Period

       

       

      YEAR is a field YEAR_ACCOUNTING, Period is a field called PERIOD in the script.

       

      I have set up 2 variables, Reference_Year = YEAR_ACCOUNTING and Reference_Period = PERIOD

       

      I want to see accumulated accounting amount for Year = Selected year and Period = Selected Period

       

       

      I have tried the following

       

      Sum ({$<YEAR_ACCOUNTING = {$(vReference_Year)}, PERIOD = {'<= {$(vReference_Period)'} >}[ACCOUNTING AMOUNT])

       

      but I don't get any data then.

       

      I think this is an easy subject to solve.

       

      Could you help there?

       

      Thank you

       

      Luc

        • Re: YTD figures based on Selection.
          Chris Hopkins

          Hi, try this..

           

          =Sum ({<YEAR_ACCOUNTING={"$(vReference_Year)"},PERIOD={"<=$(vReference_Period)"}>}[ACCOUNTING AMOUNT])

            • Re: YTD figures based on Selection.
              Luc CELESTIN

              Hello Chris

              Thanks for this answer.

              I have tried with " or with ', but i still don't get any data.

              I don't really understand what is happening. For me this formula looks fine. if I just put

               

              =Sum ({$}[ACCOUNTING AMOUNT])

              then I need to select all periods to get the up to date data. this is work around but I was expecting to select only one period and that it would give me YTD figures.

              What do you think?

              Thank you

              Luc

               

               

                    De : Chris Hopkins <qcwebmaster@qlikview.com>

              À : Luc CELESTIN <luc.celestin@yahoo.com>

              Envoyé le : Lundi 24 octobre 2016 15h16

              Objet : Re:  - YTD figures based on Selection.

                  

               

               

               

               

              |

               

               

               

               

               

               

               

              YTD figures based on Selection.

              reply from Chris Hopkins in Scripting - View the full discussionHi, try this.. =Sum ({<YEAR_ACCOUNTING={"$(vReference_Year)"},PERIOD={"<=$(vReference_Period)"}>}[ACCOUNTING AMOUNT])

              Reply to this message by replying to this email, or go to the message on Qlik Community

              Start a new discussion in Scripting by email or at Qlik Community

              Following YTD figures based on Selection. in these streams: Inbox

               

               

               

               

               

                 © 1993-2016 QlikTech International AB |  Copyright & Trademarks | Privacy | Terms of Use | Software EULA

                • Re: YTD figures based on Selection.
                  Chris Hopkins

                  hi,

                  see attached. i think this is what you're ! asking but correct me if i am wrong.

                    • Re: YTD figures based on Selection.
                      Luc CELESTIN

                      Hello Chris

                       

                      This is much better. It works now for Year but I have a problem with Period. Maybe it comes from the structure I have

                       

                       

                      For me it should not cause issues

                       

                       

                      but can be

                       

                      Thank you

                       

                      Luc

                        • Re: YTD figures based on Selection.
                          John Witherspoon

                          I suspect your data model is causing a problem with the expression, yes. You have a YEAR_ACCOUNTING in the GENERAL_LEDGER table. Select that, and you get only matching cost centers and accounts and intervals and from dates and to dates and so on. You basically need to deselect that with set analysis, and probably any calendar or intervalmatch fields that can be selected. I also don't know why we need to use variables here. Something like this:

                           

                          sum({<YEAR_CALENDAR={'$(=max(YEAR_ACCOUNTING))'},PERIOD={"<=$(=max(PERIOD))"},ACCOUNTING_DATE=,INTERVAL=,[FROM DATE]=,[TO DATE]=>} [ACCOUNTING AMOUNT])

                            • Re: YTD figures based on Selection.
                              Luc CELESTIN

                              Hello John

                               

                              Your solution is good. It works perfectly now.

                               

                              For Variable, I was thinking we should be using it for such things but too be honest, even if I did develop quite some reports in Qlikview, I never received a proper training.

                               

                              Thanks a lot for your help

                              Luc

                                • Re: YTD figures based on Selection.
                                  John Witherspoon

                                  I like variables if I'm using the same expression in multiple places, and if I'm pretty sure I'll want all of them to change if I change any of them. That way I only have one spot to maintain it rather than many. I think I've gotten in trouble in the past, though, with nested variables. Something about how QlikView tries to do everything in parallel, and didn't understand that some variables had to be computed before other variables. That was a long time ago, and I'm not sure if it's a problem in current versions.