3 Replies Latest reply on Jun 15, 2018 1:31 AM by Simon Runc

Creating A Set or Group Basis Calculation

This is for an accounting process - the process matches debit and credit in books of records and wherever there is a mismatch they call it Break.

For every break they have a reason called as Exception.

I need to create a viz which should show top 9 reasons for breaks and rest all should be 1 category and the data has more than 24 such categories.

So total 10 break reason [Exception] would come this can’t be done manually because these exceptions change daily basis break volume.

I have tried creating a set of Top 9 Exception types and then tried creating a calculated filed :

if [Exception Type]!= [Top 9 Exception Reasons] then "others"

else [Top 9 Exception Reasons]

end

This throws as error saying: Cant compare string and boolean values.

• 1. Re: Creating A Set or Group Basis Calculation

hi Shivi,

So when you create a set it is just a boolean expression (In or Out) for the elements which meet (for in) or don't meet (for out) the condition of being in that set.

As such all you need is

if  [Top 9 Exception Reasons] then [Exception Type]

ELSE 'Others'

END

Let me know if that doesn't do the trick

• 2. Re: Creating A Set or Group Basis Calculation

Thanks Simon, this worked like charm. Really what i was looking for.

Last thing: i know this is very tricky but suggest if there is a workaround. I am adding a pic of the viz i have created, but suggest if this can be sorted where the category "Others" comes at the bottom of the chart, i can manually do it but with the new data it will not work.

• 3. Re: Creating A Set or Group Basis Calculation

hi Shiva,

So with regards sorting there are several ways we could go. One way is like this (I've recreated your situation in the attached) but also created this calculation

[Top 3 Other Sorter]

IF [Top 3 Categories] THEN [Sales]

ELSE [Sales]-1000000 END

You can then use this field to sort the dimension (you may need to make the -10000000 number larger depending on your data). This way the Top 3 retailer their sort, but Other will always be last.

1 of 1 people found this helpful