Using GetFieldSelections as a Dimension in a AGGR

[William]

Hi All,

Thanks in advance for any assistance.

I have a AGGR formula that works when I hard code the Dimensions but since I need to pull the Dimensions from User selections I need to use a variable as the one of the Dimension.

The not working formula:

Sum(Aggr(Sum({<[Invoice Date]={"<=$(=Max([Invoice Date])) >=$(=monthstart(addmonths(Max([Invoice Date]), -$(VTopN))))"}>} $(eSales))
/ Sum({<[Invoice Date]={"<=$(=Max([Invoice Date])) >=$(=monthstart(addmonths(Max([Invoice Date]), -$(VTopN))))"}>}CAL_BUSINESS_DAY_IND), Company, $(vTimeDimension)))

Inline Table the User can select from in a drop down on the sheet:

VarianceTime:
Load * Inline [
TimeValue, TimeLabel
1, Var Quarter
2, Var Month
3, Var MtD
4, Var Week
];

vTimeDimension gets values from this formula

if(Index(GetFieldSelections([TimeLabel]), 'Var MtD') > 0, 'MonthToDate')
   if(Index(GetFieldSelections([TimeLabel]), 'Var Month') > 0, '[Acctg Period]',
      if(Index(GetFieldSelections([TimeLabel]), 'Var Week') > 0, '[Week Start]', '[Year Quarter]')

But vTimeDimension isn't evaluating the 'if' statement but instead contains the whole statement and this causes the AGGR formula not to work correctly.

Any help would be appreciated.

[William]

Maybe a little more explanation might help.

The function is calculating the Average Sales per a day for the amount of data the user selects and then display the results in a pivot chart.

It starts out by limiting the amount of data considered, $(VTopN) contains the number of Months to consider by using the Set Analysis '{<[Invoice Date]={"<=$(=Max([Invoice Date])) >=$(=monthstart(addmonths(Max([Invoice Date]), -$(VTopN))))"}>}'

Then it divides Sales '$(eSales)' by the number of business days 'CAL_BUSINESS_DAY_IND'.

Then it aggregates this data by 'Company' and a time period selected by the user from a drop '$(vTimeDimension)'.

This is then displayed in a Pivot Chart on the sheet.

Also there is a mistake in my vTimeDimension code, it should show this:

if(Index(GetFieldSelections([TimeLabel]), 'Var MtD') > 0, 'MonthToDate',
if(Index(GetFieldSelections([TimeLabel]), 'Var Month') > 0, '[Acctg Period]',
if(Index(GetFieldSelections([TimeLabel]), 'Var Week') > 0, '[Week Start]', '[Year Quarter]')
))

If anyone has any questions I would be happy to answer them. I figure it must be something obvious I'm doing wrong I just don't see it.

[marcus_sommer]

Try it with:

Sum(Aggr(Sum({<[Invoice Date]={"<=$(=Max([Invoice Date])) >=$(=monthstart(addmonths(Max([Invoice Date]), -$(VTopN))))"}>} $(eSales))
/ Sum({<[Invoice Date]={"<=$(=Max([Invoice Date])) >=$(=monthstart(addmonths(Max([Invoice Date]), -$(VTopN))))"}>}CAL_BUSINESS_DAY_IND), Company, $(=vTimeDimension)))

whereby you don't really need this variable-stuff - just providing the appropriate fieldnames within your inline-table respectively the target-listbox will be enough, like:

VarianceTime:
Load * Inline [
TimeValue, FieldName
1, Year Quarter
2, Acctg Period
3, MonthToDate
4, Week Start
];

Sum(Aggr(Sum({<[Invoice Date]={"<=$(=Max([Invoice Date])) >=$(=monthstart(addmonths(Max([Invoice Date]), -$(VTopN))))"}>} $(eSales))
/ Sum({<[Invoice Date]={"<=$(=Max([Invoice Date])) >=$(=monthstart(addmonths(Max([Invoice Date]), -$(VTopN))))"}>}CAL_BUSINESS_DAY_IND), Company, [$(=FieldName)]))

- Marcus

[William]

Hi Marcus,

Thanks for the suggestions.

I tried your first suggestion '$(=vTimeDimension)' and it had no effect.

I was kind of hoping not to change any Visuals  because the Users are a little 'picky' and not very tech savvy but I guess I might have to. I will try your second suggestion and let you know soon if it works. 

Thanks again.

[marcus_sommer]

What happens if you creates two textboxes with:

vTimeDimension and $(=vTimeDimension)

Beside this you could also stay by your TimeLabel and adding the FieldName as third field to the inline-table (and of course you need to set the listbox to ShowAlwaysOneValue to ensure that there is always only a single value).

- Marcus

[William]

Hi Marcus,

Thanks for the new ideas.

vTimeDimension shows nothing in a text box but $(vTimeDimension) and $(=vTimeDimension) both show the correct value, exactly what I expect to see. This is what is confusing me, shouldn't it also work in the Aggr function like it does in the text boxes? I've also tried '$(vTimeDimension)', "$(vTimeDimension)", [$(vTimeDimension)], $(=$(vTimeDimension))), =vTimeDimension, '=vTimeDimension', "=vTimeDimension" and nothing works. LOL, I know you are thinking some of the above are ludicrous and would never work but I got a little desperate.

Actually what I decided to do was just change the incoming data field names so the Users still see what they expect.

[William · Accepted solution]

Thanks Marcus for all your help, your suggestions got me to the answer that works best for me.

This is what I finally found works best for me:

Sum({<[Invoice Date]={"<=$(=Max([Invoice Date])) >=$(=monthstart(addmonths(Max([Invoice Date]), -$(VTopN))))"}>} Aggr(Sum($(eSales))
/ Sum(CAL_BUSINESS_DAY_IND), Company, [$(=TimeLabel)]))