topic Re: Assistance with Expression in Chart in QlikView

Assistance with Expression in Chart

QFanatic — Fri, 09 Apr 2021 13:25:19 GMT

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

Re: Assistance with Expression in Chart

rubenmarin — Sun, 11 Apr 2021 10:15:24 GMT

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

Re: Assistance with Expression in Chart

QFanatic — Sun, 11 Apr 2021 13:42:18 GMT

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

Re: Assistance with Expression in Chart

QFanatic — Sun, 11 Apr 2021 13:51:57 GMT

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

Re: Assistance with Expression in Chart

rubenmarin — Sun, 11 Apr 2021 18:47:49 GMT

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.

Re: Assistance with Expression in Chart

QFanatic — Mon, 12 Apr 2021 08:41:25 GMT

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.