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

[patricesalem]

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

[dplr-rn]

see below link

Referencing Measures in other Measures

[patricesalem]

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

[patricesalem]

Hello

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

any idea ?

thanks

[dplr-rn]

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

[dplr-rn]

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

[patricesalem]

Dilip

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

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

thanks

[dplr-rn · Accepted solution]

ah ok. i think you may need variables with parameters. check below

Variables with Parameters

i have not used them much before though

[patricesalem]

Great!!!

I made a quick test and it works like a charm !

To summarize

in expression of the gauge : $(vTest('10'))  -> 10 being my product family -> no = sign

In variable, the $1 syntax catches the variable sent from the expression :

sum({<[Week_Sales_per_Week]={'40'},[Year_Sales_per_Week]={'2018'},Objective_Product={$1}>}Qty_per_Week)

Absolutely fantastic.

thanks again for your help Dilip

[dplr-rn]

Great to hear.