topic Re: Exclude Selections but keep set analysis in QlikView

Exclude Selections but keep set analysis

Anonymous — Wed, 26 Jul 2017 09:15:04 GMT

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

Re: Exclude Selections but keep set analysis

stigchel — Wed, 26 Jul 2017 09:45:26 GMT

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)

Re: Exclude Selections but keep set analysis

PrashantSangle — Wed, 26 Jul 2017 09:51:10 GMT

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

Re: Exclude Selections but keep set analysis

Kushal_Chawda — Wed, 26 Jul 2017 10:09:19 GMT

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

Re: Exclude Selections but keep set analysis

Anonymous — Wed, 26 Jul 2017 10:15:21 GMT

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

Re: Exclude Selections but keep set analysis

Anonymous — Wed, 26 Jul 2017 10:16:55 GMT

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

Re: Exclude Selections but keep set analysis

PrashantSangle — Wed, 26 Jul 2017 10:22:22 GMT

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

Re: Exclude Selections but keep set analysis

Kushal_Chawda — Wed, 26 Jul 2017 10:59:00 GMT

If you can share the sample it will be helpful

Re: Exclude Selections but keep set analysis

sunny_talwar — Wed, 26 Jul 2017 11:27:57 GMT

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)

Re: Exclude Selections but keep set analysis

Anonymous — Wed, 26 Jul 2017 12:00:38 GMT

Perfect. Thanks