Thoughts on moving this if statement to a flag

[#1]

I have an input box that allows me to set a variable based on what is selected from the drop down menu.

Then my pivot table changes to display the field based off the input box selection using this if statement.

=if([Date Type]='Week', InvoiceWeek, if([Date Type] = 'Year', InvoiceYear, if([Date Type] = 'Month', InvoiceYearMonth, if([Date Type] = 'Day', InvoiceDay))))

This seems to take up a significant amount of CPU speed during the refresh and was curious if anybody had any thoughts on optimizing this for best performance or how I could move this to be flag based?

Thanks,

[#2]

Step1:

Create A Varibale with v_variable1

=if([Date Type]='Week', 'InvoiceWeek', if([Date Type] = 'Year', 'InvoiceYear', if([Date Type] = 'Month', 'InvoiceYearMonth', if([Date Type] = 'Day', 'InvoiceDay'))))

// I have changed field names to String.

Step 2:

Change the expression in the Pivot table to

=[$(v_variable1)]

Let me know , if this works for you.

[#3 · Accepted solution]

You could use a slider bar in conjunction with a variable and the conditional show feature to make different list boxes/charts available. See attached qvw.

I've used this a number of times. Probably a more elegant way to do it. But, as you noticed, nested if statements (especially using Text values) take a significant amount of CPU.

[#4]

sjprows

Does this effect performance having those objects hidden?

[#5]

It doesn't. Conditionally hidden, and minimized objects do not calculate.

We are using very large data sets, and this approach has given us the flexibility and performance we are looking for.

[johnw]

So you are using this as a dimension in the pivot table? How about a cyclic group of InvoiceYear, InvoiceMonth, InvoiceWeek and Invoice Day? Call it InvoicePeriod. Use InvoicePeriod as your chart dimension. It's not EXACTLY what you asked for, but I think it performs the same function, and more cleanly. I also like to use the default controls when possible, as it helps keep relearning between applications to a minimum.

[#7]

Thanks John, I actually decided to use this instead. Your input was much appreciated.