Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Exclude Selections but keep set analysis

Hi I have a chart that calculates and shows the most recent FTE.  The problem I have is that if I change the 'Month' or 'Year' selection the values disappear.  Below is the expression:

Sum(

{<

Employed_Staff.Headcount_Date = {'$(=Max(Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'}>}

  Employed_Staff.FTE)

I tried the following too, but this also loses the data when I select a month or year:

Sum(

{1< Employed_Staff.Headcount_Date = {'$(=Max(Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'}>}

Employed_Staff.FTE)

I did think that I could detach the chart, but not sure if it will refresh when the new week's data comes into the report?

Is there a better way to do this?

Regards

Phil

1 Solution

Accepted Solutions
Highlighted

On similar lines, may be this

Sum({1<Employed_Staff.Headcount_Date = {'$(=Max({1}Employed_Staff.Headcount_Date))'}, Employed_Staff.Weekly_Monthly={'Weekly'}>} Employed_Staff.FTE)

View solution in original post

9 Replies
Highlighted
Partner
Partner

Try with {1} in the dollar sign expansion

Sum(

{<

Employed_Staff.Headcount_Date = {'$(=Max({1} Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'}>}

  Employed_Staff.FTE)

Highlighted

Try what piet suggested or nullify the field on which you dont want change calculation like

Sum(

{<

Employed_Staff.Headcount_Date = {'$(=Max(Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'},Year=,Month=>}

  Employed_Staff.FTE)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
MVP
MVP

Please check for that Year and Month selection any value is associated or not based on expression logic.

Highlighted
Creator
Creator

None of those work.

My Year & Month is from a table which is a linked table based on Staff.Headcount_Date.

Could that have anything to do with it?

Regards

Highlighted
Creator
Creator

It works if the current selection includes the latest week though or if there is no selection made.

Highlighted

One more guess

Sum(

{<

Employed_Staff.Headcount_Date = {'$(=Max({<Year=,Month=>}Employed_Staff.Headcount_Date))'},

Employed_Staff.Weekly_Monthly={'Weekly'},Year=,Month=>}

  Employed_Staff.FTE)

Let us know if it work for you or share your app with sample data.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
MVP
MVP

If you can share the sample it will be helpful

Highlighted

On similar lines, may be this

Sum({1<Employed_Staff.Headcount_Date = {'$(=Max({1}Employed_Staff.Headcount_Date))'}, Employed_Staff.Weekly_Monthly={'Weekly'}>} Employed_Staff.FTE)

View solution in original post

Highlighted
Creator
Creator

Perfect.  Thanks