Hi all, im struggling to get a formula created which sums up all max values from a calculated field, get the correct data for a database field which doesnt provide a number but TRUE/FALSE string and get a value out of a database where the answer is not within the same line.
For the first problem the data comes from a data table out of an internal audit tool.
break_seats
parseint(ifelse(question=‘Enter the amount of useable seats for associates during break.’,toString(answer),‘0’))
Within the pivot table (2.) im going to use sum from the latest audit per creation date per filter to only show the last value (duplicates 1 or 0).
in the above section (1.) i want to sum up all max values from the pivot table (2.), which should be 544 currently.
Im able to calculate that within the pivot with
test
sumOver(max({break_seats}),[{audit_name}])
which gives the 544. Using the same test calculated field within a KPI indicator field provides the max out of all values. What is wrong?
For the 2nd problem i have the following raw data, where i need to look for a specific question as calculated field and use the answer as value from a standard audit.
While converting numbers isnt a problem im struggling with the questions which provides a TRUE or FALSE as string. What im looking for is
if the question is “Are driver associates able to get free coffee or tea at your site” and the answer = “TRUE” then output is 1 otherwise 0.
The 3rd problem i have is another audit where a special type of an item is selected (question: staging cart type / answer: e.g. 2.0 Carts) but the quantity is within another row
I want to have a column named staging cart type and one with the amount as per screen
Hello @dggoumah, I will try to provide solutions or work-arounds to each of these questions as you asked them.
Some calculations run easier in pivot tables since they are partitioned within the visual. What I would recommend for the KPI is to create a new calculated field and changing the nested max function to maxOver. It would look like this: sumOver(maxOver({break_seats}, [{audit_name}], PRE_AGG), [], PRE_AGG)
That should get the max value from break seats across audit_names and then sum the max across the dataset. If you need to run this calculation for a time period, it can be added into the partition brackets of the sumOver.
For the 2nd problem, you can use an AND statement in your ifelse calculation to check for the question value and the answer value. It would look like this: ifelse(question = 'Are driver associates able to get free coffee or tea at your site' AND answer = 'TRUE', 1, 0)
As for the third question, creating columns like that likely won’t function as expected, but I can at least send some suggestions to see if we can make something work. Maybe by utilizing the contains and substring calculations, we could make something work. Contains will check if the Staging Carts and Type strings are within the question, then you can use substring to return the Type value in combination with locate: ifelse(contains({question}, 'Staging Carts') AND contains({question}, 'Type'), substring({question}, locate({question}, 'Type'), strlen({question}) - locate({question}, 'Type'), NULL)
You may need to alter the substring portion depending on what you want to return, but this should get you close to a column for Staging cart type. I hope this helps!
Hello @dggoumah, is this issue only occurring for the da_coffee field or is it happening with all of the calculated fields? Maybe it is an issue with case sensitivity on either the question or answer fields. If you switch if statement in the calculated field to use contains, maybe it would function correctly: ifelse(contains({question}, ‘Are driver associates able to get free coffee or tea at your site’, CASE_INSENSITIVE) AND contains({answer}, 'TRUE', CASE_INSENSITIVE), 1, 0)
Also, double check that your question field is exactly the same as the field value being returned, it could be a very small mistake that would cause this to fail. Let me know if this helps!
Hello @dggoumah, did my last response help resolve the issue you are facing? I will mark it as the solution, but please let me know if you still need assistance. Thank you!
