9 Replies Latest reply: Oct 29, 2018 7:07 PM by Dilip Ranjith RSS

    Gauge chart - use of KPI result and max field as variable

    patrice salem

      Hello

       

      I have two very long expressions to calculate the measure field (KPI indicator = sales ) and the max value field (=targets

      I would like to use both fields to run a % calculation showing the % of sales vs target. Instead of copying both expressions into one field, is there a way to call both field names into an expression.

      I read it is possible with bar charts using label names...but I don't manage to do it with my 8 gauges charts.

      Thanks for your help

      pat

          • Re: Gauge chart - use of KPI result and max field as variable
            patrice salem

            Hi Dilip

             

            Thanks for pointing this link but it does not help for gauges. It makes refence to Column()...but no help for me.

            Any other idea on how to get the value of the unique dimension and the value of the Max. field ?

             

            I would like to divide one by the other and put the result next to the label of the gauge (without to have to recopy the 30 lines of code of each measurement)

             

            thanks

              • Re: Gauge chart - use of KPI result and max field as variable
                patrice salem

                Hello

                 

                I'm trying to find a workaround using GetObjectMeasure(0)...but it does not retrieve the result of the calculation

                 

                Image 1860.jpgImage 1862.jpg

                Image 1861.jpg

                 

                any idea ?

                thanks

                • Re: Gauge chart - use of KPI result and max field as variable
                  Dilip Ranjith

                  Hi Patrice

                  The answer to the question posted in the link points yo effective usage of variables and reusing them to create new expressions.

                  I didn't mean suggesting using column functions

                  Maybe I misunderstood your need

                    • Re: Gauge chart - use of KPI result and max field as variable
                      Dilip Ranjith

                      Basically e.g. use 1 variable1 for sales another one variable2 for target. Use variable1- variable2 as the 3rd measure

                        • Re: Gauge chart - use of KPI result and max field as variable
                          patrice salem

                          Dilip

                           

                          In the same view, I have 9 gauges. Each of them contains the sales measure + the target measure (used for max).

                          Image 1867.jpg

                          So, it means that I would have to create 18 variables ?

                           

                          Ideally, I would create 2 variables. Each variable would use the same code but how would I change the product family for each gauge ?

                           

                          Here is the code for the measure (value that changes from one gauge to another : [Family_Efficy_Product]={'10'})

                           

                           

                           

                          =

                          pick (vTableScoreCard,

                          //full year

                           

                           

                          sum({<[Week_Sales_per_Week]={"<$(VCurrentWeek)"}-1,[Year_Sales_per_Week]={$(vReferenceYear)},[Family_Efficy_Product]={'10'}>}Qty_per_Week)

                          ,

                          //ytd

                          sum({<[Week_Sales_per_Week]={"<$(VCurrentWeek)"}-1,[Year_Sales_per_Week]={$(vReferenceYear)},[Family_Efficy_Product]={'10'}>}Qty_per_Week)

                          ,

                          //semester

                          sum({<Year_Sales_per_Semester={$(vReferenceYear)},Semester_Sales_per_Semester={$(vCurrentSemester)},[Family_Efficy_Product]={'10'}>}Qty_per_Semester)

                           

                           

                          ,

                          //quarter

                          sum({<Year_Sales_per_Quarter={$(vReferenceYear)},Quarter_Sales_per_Quarter={$(vCurrentQuarter)},[Family_Efficy_Product]={'10'}>}Qty_per_Quarter)

                           

                           

                          ,

                          //month

                          sum({<Year_Sales_per_Month={$(vReferenceYear)},Month_Sales_per_Month={$(vCurrentMonthNum)},[Family_Efficy_Product]={'10'}>}Qty_per_Month)

                           

                           

                          ,

                          //last week

                          //week(date(floor((yearend(Today(), -1)))))  -> to get last week number of last year (it can be 52 or 53)

                           

                           

                          if (VCurrentWeek='1',

                          sum({<Week_Sales_per_Week={$(LastWeekPreviousYear)},Year_Sales_per_Week={'$(=$(vReferenceYear)-1)'},[Family_Efficy_Product]={'10'}>}Qty_per_Week)

                           

                           

                          ,

                          sum({<Week_Sales_per_Week={'$(=$(VCurrentWeek)-1)'},Year_Sales_per_Week={$(vReferenceYear)},[Family_Efficy_Product]={'10'}>}Qty_per_Week)

                           

                           

                          )

                          ,

                          //current week

                          sum({<Week_Sales_per_Week={$(VCurrentWeek)},Year_Sales_per_Week={$(vReferenceYear)},[Family_Efficy_Product]={'10'}>}Qty_per_Week)

                           

                           

                          ,

                           

                           

                          //8

                          //full previous reference year target vs full previous reference year volumes

                           

                           

                          sum({<Year_Sales_Full_Year={'$(=$(vReferenceYear)-1)'},[Family_Efficy_Product]={'10'}>}Qty_per_Year)

                           

                           

                           

                           

                           

                           

                          ,

                          //value 9 (is shown at the top entries in combobox list

                          //Target reference year previous semester vs reference year previous semester volumes

                          //if current semester is 1, we look at previous year sem2 sales

                          if ( vCurrentSemester='1',

                          sum({<Year_Sales_per_Semester={'$(=$(vReferenceYear)-1)'},Semester_Sales_per_Semester={'2'},[Family_Efficy_Product]={'10'}>}Qty_per_Semester)

                          ,

                          sum({<Year_Sales_per_Semester={$(vReferenceYear)},Semester_Sales_per_Semester={'1'},[Family_Efficy_Product]={'10'}>}Qty_per_Semester)

                           

                           

                          )

                           

                           

                           

                           

                          ,

                          //10

                          //Target reference year previous quarter vs reference year previous quarter volumes

                          if (vCurrentQuarter='1',

                          sum({<Year_Sales_per_Quarter={'$(=$(vReferenceYear)-1)'},Quarter_Sales_per_Quarter={'4'},[Family_Efficy_Product]={'10'}>}Qty_per_Quarter)

                           

                           

                          ,

                          sum({<Year_Sales_per_Quarter={$(vReferenceYear)},Quarter_Sales_per_Quarter={'$(=$(vCurrentQuarter)-1)'},[Family_Efficy_Product]={'10'}>}Qty_per_Quarter)

                           

                           

                          )

                           

                           

                          ,

                          //11

                          //Target reference year previous month vs reference year previous month volumes

                          if (VCurrentMonth='1',

                          sum({<Year_Sales_per_Month={'$(=$(vReferenceYear)-1)'},Month_Sales_per_Month={'12'},[Family_Efficy_Product]={'10'}>}Qty_per_Month)

                          ,

                          sum({<Year_Sales_per_Month={$(vReferenceYear)},Month_Sales_per_Month={'$(=$(VCurrentMonth)-1)'},[Family_Efficy_Product]={'10'}>}Qty_per_Month)

                           

                           

                          )

                           

                           

                           

                           

                          )

                           

                          The pick fonction refers to

                          Image 1868.jpg

                          thanks