I have a formula that I want to use to determine which slice of the pie an entity should be counted in. This is the SQL I can use to derive the data:
SELECT COUNT(d.DataSourceKey) , d.CalcStat
SELECT datasourcekey, Missing = SUM([Missing File Count]), NotConfirmed = SUM([Not Confirmed File Count]), Confirmed = SUM([Confirmed File Count])
, CalcStat = IIF(SUM([Missing File Count]) <> 0, 0, IIF(SUM([Not Confirmed File Count]) <> 0, 1, 2))
WHERE FileDateKey = 20180215
GROUP BY DataSourceKey
GROUP BY d.CalcStat
I've been successful in Sisense to add this formula (CalcStat) to a pivot table and I can see the results I am expecting for each firm (in our case 1370 rows).
I've been trying to figure out a way to put that formula into the Category for the pie chart (I want to have my pie chart to have 3 slices (0, 1,2). I've looked at the DynamicBuckets plugin, but it doesn't seem to be doing exactly what I want either.
I tried changing the chart type to a pie and then moving the Formula to the Categories, but of course that didn't work.
In reality the 0, 1, and 2 represent strings but I wasn't able to figure out how to get strings back from a function in the Values area.The reason I need the formula is that the category is based on the sum of 3 different fields depending on what filters are selected.
Please sign in to leave a comment.