12 Replies Latest reply: Nov 10, 2015 4:47 AM by Andy Weir RSS

    delivery

    Fabio Mazzarelli

      hi community,

       

      I have several years online . What is the formula to extract all kilograms shipped separately in 2011, 2012, 2013, 2014 and in 2015 ?
        • Re: delivery
          Andy Weir

          COuld youpost some sample data .  So we can help you better.

            • Re: delivery
              Fabio Mazzarelli

              hi,

              year, totalKg

              2010 1

              2010 1

              2010 1

              2010 1

              2011 6

              2011 7

              2011 1

              2011 2

              2012 4

              2013 1

              2013 1

              2013 5

              2013 20

              2014 2

              2014 5

               

              I would like to use a kpi

              2010                    2011               2012               2013                     2014                    2015

              Year 2010               Year               Year               Year                    Year                    Actual  Year

              Total 4                    16                    4                    27                         2                         0

            • Re: delivery
              shair abbas

              on script level you can use following script

              Load

              Sum(totalKg) as totalKg,

              Year

              from table

              group by year;

               

              and on front end just use simple table against this two fields..

               

              OR


              on front end chose any graph expect gauge chart then add dimension year and in expression add this

              expression sum(totalKg)

              • Re: delivery
                Fabio Mazzarelli

                Hi,

                 

                I need s function sum(kg, if(year= 2010) or year=2011, year=2012 year=2013 year=2014 year=2015

                • Re: delivery
                  Christoph Hebig

                  Hi Fabio,

                   

                  you can go with Set Analysis to select one year at a Time like this:

                   

                  Sum({<Year = {'2010'}>}kg) -> with this you'll get a measure you can use in a KPI which has all KG Values summed of Year 2010

                   

                  If you need this in a Bar-Chart, you need to use Year as a Dimension and then just sum(KG) against this.

                   

                  Sadly there is no KPI which shows many numbers at a time, so you'll need to use the Pivot-Table to do this, which works same as the Bar-Chart version.

                   

                  Hope i helped you!

                   

                  Regards

                  Chris

                   

                  P.S.: Please mark Helpful/Correct Answers as such.

                  • Re: delivery
                    Fabio Mazzarelli

                    Hi Community,

                     

                    i post again my needs.

                     

                    I have to sum a value if a condition is verified

                     

                    That means;

                    sum(data)

                    if year(today()) = 'vYear (input variable)

                     

                    Example:

                     

                    sum(Kg) if 2015 = 2015 (input variable)

                     

                    In Excel I use the function sumif

                     

                      • Re: delivery
                        Christoph Hebig

                        So I was nearly correct, just replace the '2010' in this formula: sum({<Year = {'2010'}>}KG) with your Variable, so you'll get the sum of KG for the year that is defined in your Variable.

                         

                        Regards

                        Chris

                        • Re: delivery
                          Andy Weir

                          Lets try to break this down from your various posts.

                           

                          I think you want to display the result as a KPI visualisation.

                           

                          The input parameter to the KPI is a year or multiple years of Kg data?

                           

                          If its one year you can use the qsVariable approach I've given you before to create a drop down of years and populate a variable for use in your measure.

                           

                          Sum({<Year = $(vYear)>}kg)

                           

                          For multiple years a simple SUM(Kg) should suffice based on the data example you published as users selection/de selection of years will drive the total Kg.