5 Replies Latest reply: Jun 8, 2016 6:29 AM by Sunny Talwar

# How to define an exact range

I'm using NetWorkDays(FirstDate,LastDate) to calculate the time on task value that I want to show in a summarized way. I use a pivot table for the visualization with this value as dimension and another value that I count. The question is how to define an exact range for the dimension (for instance 1,2,3,4,5,6-10,11-20,21-50,>50), not just the first ten values. Is it possible without changing the SQL code?

• ###### Re: How to define an exact range

Would you be able to share what you see right now and may be share what you would like to see?

• ###### Re: How to define an exact range

Right now: -,Other,1,2,3,4,5,6,7,8,9 (dimension restricted to a fix number of columns)

Goal: -,1,2,3,4,5,5-10,>10

• ###### Re: How to define an exact range

May be using if statement:

If(Expression <= 5, Expression,

If(Expression > 5 and Expression <= 10, Dual('5-10', 6), Dual('>10', 11)))

• ###### Re: How to define an exact range

Looks good, I think this is will be my version:

If(Expression <= 10, Expression,

If(Expression > 10 and Expression <= 15, Dual('11-15', 11),

If(Expression > 15 and Expression <= 20, Dual('16-20', 12),

Dual('>20', 13)))

Do I have to replace all expression with my formula NetWorkDays(FirstDate,Lastdate) or is it possible to use variables?

• ###### Re: How to define an exact range

Yes, you will need to replace Expression with whatever you are currently using. Try and see if it works or not