Since originally posting I've spent a lot of time trying to get applymap to work the way I need it to and have not yet been able. The main data source I am using is from one table which houses the [Diff Value] and the [Grade].
- [Diff Value] is the numerical value that can be used to make an accurate average.
- [Grade] is a text string that puts the numerical value back into the proper format for output.
Ultimately, I want to take all of the raw data with hundreds of climbs, find the average [Data.Diff Value] for each month, but have the output be in terms of [Data.Grade]. I've been able to make a pivot table to get through the first part but not get the correct output.
- Dimension = Data.autocalendar.YearMonth
- Measure = Round(Avg([Data.Diff Value]), 0.2)
This gets me to the place where I have the average [Diff Value] for each month, but not I don't know how to get it to report those results as [Grade].
When I tried my new applymap value [finalgrade] to the above pivot table it will not output any values.
Here is the code I was using when trying apply map:
// Load mapping table of grades:
Mapping Load [Diff Value], [Grade] From [lib://AttachedFiles/Climbing Test.xlsx]
(ooxml, embedded labels, table is Data);
ApplyMap('map1', [Diff Value], null()) as [finalgrade]
From [lib://AttachedFiles/Climbing Test.xlsx]
(ooxml, embedded labels, table is Data);
I think I have finally arrived at the correct question to ask – thanks for your patience. From my understanding, both applymap and the "if statement" serve as a static way to get the [Diff Value] mapped to the appropriate text string. I went back and adjusted my source table to include values for 9.2 and 9.6 etc so that I now have all of that mapped appropriately.
The real problem is that when I go to make my table, I have to use [Date.autocalendar.YearMonth] as the dimension, and a calculated field, =Round(Avg([Diff Value]),0.2), as my first measure. When I use my mapped field [FinalGrade] as a second measure, it is applying it to the dimension [Date.autocalendar.YearMonth] and so I get a bad result.
I think the answer is that I need some sort of calculated dimension. I need a dimension that will give me the average [Diff Value] based on either [Date.autocalendar.YearMonth] or simply [Date]. Then if I apply my [FinalGrade] map as a measure I think it will work.
Thanks again for your help.
i guess i too was not that clear.
you have added one dimension, and a calculated field as measure. now add another calculated field with if statement and give the conditions if it lies between this number than this grade and so on . hopefully it will work for you.
measure 2= if (avg <8 and avg>=7 then grade = 5.7) use nested if to add all the condition.
Thanks Pradosh - that worked perfectly, though it does seem a little redundant to have a chart already mapping values to text strings and only have that available through the script and not expressions in the active app.
This solved my biggest problem, but now I wonder (if you have the patience) if you know of a solution to trying to graph this data.
Ideally I want a chart like this:
where dimension = date.yearmonth
measure 1 = number of routes
measure 2 = FinalGrade (I saved the long list of nested "if statements" from my table)
Only problem is that, obviously, we just worked to make all of these values text strings instead of numbers, but the graph won't accept text strings for the y-axis (secondary axis).
I thought I could get around it by using the dual() function in my nested if statements, and it does indeed get the correct line plotted, but when I hover over the data point, it's label is the number and not the text string. Any way around this? Here is a sample of the code I was using:
if(avg([Diff Value])<11.8 and avg([Diff Value])>=11.6,dual('5.11c',11.6),
if(avg([Diff Value])<12 and avg([Diff Value])>=11.8,dual('5.11d',11.8),
**I would like to have the graph utilize the number value for plotting the line (11.8), and the label to use the text string (5.11d).**
If that use is confusing, maybe a simpler use case would be a KPI with a dual() measure, displaying the text instead of the number – is this possible?