Linking period to a value column via a variable

[adamdavi3s]

Hi All,

This problem is vexing me.

I have a table of history values and a table of current values.

These values are linked to a period.

I want to have a table / chart which includes the period & the current amount as at that period.

I've set up a variable which tells the dashboard which column of data to return, and this works perfectly for individual periods, or any number of historical periods.

However when selecting the 'current' period along with any other period, the data isn't returned.

I sort of understand why this doesn't work (the variable can return only one answer) but I thought the variable might calculate for each period individually... I think I just need to tweak it a little but I can't figure it out!

Its kind of hard to explain in words so have put together this example!

Essentially the outcome I want is:

ALL periods   sum(CUR_AMOUNT) as at that period....

[swuehl · Accepted solution]

Just change the variable definition to a non evaluated version (removing the equal sign and the single quotes):

if(%PERIOD=vcurrent_period,CUR_CUR_AMOUNT,HIST_CUR_AMOUNT)

I think you could also concatenate all fact data into one table and just flag the historical data as such, that might be an idea, also.

Regards,

Stefan

[adamdavi3s]

Hi Stefan,

Thank you for your response.

You know I did  try that, what I totally failed to do (and I have tried about 20 different formula for this!) was simply remove the ' from around the field names in the IF statement.... I knew it had to be something simple!!

I also pondered loading everything into one table but the issue I had was around the YTD figures (As these obviously by their nature can not be summed), however I didn't think of the historical flag.

I will have a play thank you!

Kind regards

Adam