Assistance with Expression in Chart

[QFanatic]

Hello Clever People,

Please find attached.

1. A bit about the data. Dimension "Service" has a specific value, called "Bucks"

2. I have a normal calendar as well as one for Fiscal Year (which runs from July to June of next year)

3. I have "Actual" values (which are indicated with 'HasValue' = 1) and Budgeted Values ('HasValue' = 0). (You will notice that the "highest" Financial year (2021/2022) contains only values with 'HasValue' = 0 . common sense.)

4. What I want to achieve on the Graph with the blue heading..

    4.1  if the user DOESNT select a financial year - it should just go to the "latest" month that 'HasValue' = 1 MINUS 13 months.... The -13 doesnt work - I think it has to do with the Financial year in the Set Analysis...

   4.2 if the user selects a Financial year (2020/2021) = same must happen....it should just go to the "latest" month that 'HasValue' = 1 MINUS 13 months. That DOESNT work, because the Period will fall in another FY - that is why I am trying to subtract 1 from the FY. (I got the amazing advice from @rwunderlich to use Firstsortedvalue on the FY - since it is a dual field - not sure how to use 13 in the Rank part of FirstSortedValue)

5. Any advice getting this chart to work. Maybe I am off on a tangent and there is a better way to do it?

Thank you

[rubenmarin]

Hi, If set analysis is alredy filtering by period, why you need toa lso filter by FY?

Just this can work or I'm missing something?: 

sum({<Period = {'$(vRepPeriod13)'},Channel = {'Messaging'}, Table_Type = {'Mess_Grph_2'},Services = {'Bucks'}>}Value)
/
1000

[QFanatic]

Hi,

Thank you for your response.

(Please note that I've changed the value of vRepPeriod13 - it excludes any selections made on FY)

The value of vRepPeriod13, with no selections made on FY, is = 202001 - which is correct. (Please note that 'Table_Type', 'Channel' and 'Services' = 'Bucks' always need to be selected)

However, if I select FY = 2020/2021, the value in the expression disappears although vRepPeriod13 still stays = 202001...why is that happening? Because that causes my expression in the chart to disappear also. I'm getting the feeling that I should be using an 'IF' statement in the expression.. that tests - did the user select a FY - if they did - use some other 13 month calc. If they didnt - use vRepPeriod13 as current? What do you think?

Much appreciated

thank you

[QFanatic]

Hi Ruben - answer to your first question - if I subtract 13 months from any Period or PeriodID, it would fall OUTSIDE of the current FY...that is why, I think, I need to clear the FY

[rubenmarin · Accepted solution]

Hi, if only with period you have the value you need there is no need to also filter FY. You can just omit the selections on this field in set analysis like:

sum({<Services = {'Bucks'},Period = {'$(vRepPeriod13)'}, Channel = {'Messaging'}, Table_Type = {'Mess_Grph_2'}, FY=>}Value)

Without this the value will disappear because it tries to show values with Period 202001 and FY 2020/2021, and there is  no data with both conditions.

[QFanatic]

Thank you very much.

I think I got confused, because in the variable I needed to keep track of FY but not in in the expression.

Much appreciated.