Hi All,
One of my business users likes his excel, so wants all output within QV to be in tables. OK, no problem if that's what he wants!
The requirements do require different values being displayed per column based on a set of input parameters he wants to specify. For example he can choose the following to get the exact data set he wants. These are all specified using an input box and variables:
| Element | Allowed values | Comments |
|---|
| Service/Booking | Service; Booking | Defines if values are selected based on the date the hotel was booked, or when the guest is staying |
| Date Range | YTD; MTD | Is he looking for Year-to-date or month-to-date of service/booking dates |
| Show 2014 Values | Yes; No | Used for conditional display to show the previous years figures |
| Dimension | Client; Destination; Sales Hierarchy; Top Cities; Top Hotels | Controls which of the dimensions of the pivot tables are displayed using conditionals on the dimensions |
In my columns I then use nested if statements in order to execute the correct calculation. For example, in [Sales (EUR)] my expression is:
|
=if (vRange = 'YTD',
if (vDateType = 'Service',
sum($(vServiceCURYTDCriteria)SALESPRICEEUR),
sum($(vBookingCURYTDCriteria)SALESPRICEEUR)
),
if (vRange = 'MTD',
if (vDateType = 'Service',
sum($(vServiceCURMTDCriteria)SALESPRICEEUR),
sum($(vBookingCURMTDCriteria)SALESPRICEEUR)
),
if (vRange = 'WTD',
if (vDateType = 'Service',
sum($(vServiceCURWTDCriteria)SALESPRICEEUR),
sum($(vBookingCURWTDCriteria)SALESPRICEEUR)
)
)
)
)
|
I store my Set Analysis criteria in variables, as they are reused throughout the application and it makes it much easier to make changes in one place. I have 31 columns in total in this table, all of which follow the above pattern.
I hope this isn't some border case, and that actually this kind of requirement is fairly common!
So, after all that my question is: Are there any best practices defined on how to handle a requirement like this? Would I be better creating a separate column in my chart for each of the end-results above (so 6 columns instead of 1, giving me a total of 186 columns) and then using a conditional show to make sure the correct columns are displayed; or even breaking this down into multiple charts and using a conditional show at the chart level?
Have others encountered this kind of requirement? How have you approached it?
Thanks,
Rory.
