Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
QFanatic
Creator
Creator

Assistance with Expression in Chart

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

 

 

1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

5 Replies
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
Creator
Creator
Author

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
Creator
Creator
Author

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

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.

View solution in original post

QFanatic
Creator
Creator
Author

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.