
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Gauge chart - use of KPI result and max field as variable
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ah ok. i think you may need variables with parameters. check below
i have not used them much before though

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
see below link

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello
I'm trying to find a workaround using GetObjectMeasure(0)...but it does not retrieve the result of the calculation
any idea ?
thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Basically e.g. use 1 variable1 for sales another one variable2 for target. Use variable1- variable2 as the 3rd measure

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ah ok. i think you may need variables with parameters. check below
i have not used them much before though

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Great to hear.
