4 Replies Latest reply: Aug 22, 2013 11:24 AM by Carlos Duque RSS

    Display Total Per Year

    Carlos Duque

      Hi,

       

      I Found this command by searching the web and looking at the aQV demo

       

      =num(sum( {$<Year = {$(vMaxFiscalYear)}>} TotalDollar ),'$#,##0')

       

       

      this is how my year is setup in my Edit Script

      Year (date(date#(XDATE, 'YYYYMMDD'),'YYYY/MM/DD')) as Year,

       

      and this is how my Variable is setup.

      vMaxFiscalYear   =Max(Year)

       

      I the command seems to work but I'm still getting the total for all my years not the specific year that i want base on the MaxFiscalYear

       

      Please let me know if you can think of any other options to show an expecific year in a text Object.

       

      Thank you

        • Re: Display Total Per Year

          HI,

          it seems that you are missing a '=' :

          $(vMaxFiscalYear) --> $(=vMaxFiscalYear)

           

          THis is necessary if the value of your Variable is "Max(Year)" and not i.e. "2013".

          In this case it has to be evaluated and this is done with an the $(=MyVar) Syntax.

           

           

           

          Roland

            • Re: Display Total Per Year
              Carlos Duque

              Ronald,

               

              Thank you I think that was it.

                • Re: Display Total Per Year
                  Carlos Duque

                  Hi Ronald,

                   

                  sorry to bug you again but I had one last questions regarding YTD vs Last YTD

                  I'm totaling all YTD 2013 - All 2012. How can I make vLFY to read YTD applying today's date-365? as a true YTD vs LYTD. I tried using the date, month, daterange in my Variables but its not picking up the correct date or im getting errors. I also try multiple link provided by QV help pages but none seem to work for me.

                  please let me know if you have an i dean of what I should do.

                   

                  =num((sum( {$<Year = {$(=vMaxFiscalYear)}>} TotalDollar )) - (sum( {$<Year = {$(=vLFY)}>} TotalDollar )),'#,##0')

                   

                  this is how my Variable is setup.

                  • vLFY =Max(Year)-1

                  and my dates formats.

                    Year (date(date#(XDATE, 'YYYYMMDD'),'YYYY/MM/DD')) as Year,
                    Month (date(date#(XDATE, 'YYYYMMDD'),'YYYY/MM/DD')) as Month,
                    Day (date(date#(XDATE, 'YYYYMMDD'),'YYYY/MM/DD')) as Day,
                    Week (date(date#(XDATE, 'YYYYMMDD'),'YYYY/MM/DD')) as Week,
                    WeekDay (date(date#(XDATE, 'YYYYMMDD'),'YYYY/MM/DD')) as WeekDay,
                    (date(date#(XDATE, 'YYYYMMDD'),'YYYY/MM/DD')) as Daterange;

                   

                  Thank you !!!

              • Re: Display Total Per Year
                Carlos Duque

                Ronald,

                 

                I was able to find the answer.

                 

                Missing

                DATE (date(date#(XDATE, 'YYYYMMDD'),'YYYY/MM/DD')) as DATE,

                 

                =num((sum( {$<Year = {$(=vMaxFiscalYear)},Date={"<=$(=max(Date))"}>} TotalDollar )) - (sum( {$<Year = {$(=vLFY)},Date={"<=$(=date(addmonths(max(Date),-12)))"}>} TotalDollar )),'#,##0')

                 

                here's the link posted by John Witherspoon

                http://community.qlik.com/message/64478#64478