Section Access

[#1]

Having a problem getting my head round this. My current section Access works well when 1 choose 1 year. But now my client wants to be able to choose multiple years and I can't get my head around it any help will be great. I can not upload any data as its a government data set. My current script below

=num(

Sum({$<

  [Milestone Num] = {22}

  ,[Activity Measure Code]={'TS-APPL'}

  ,[Activity Period Year Quarter Desc]={"$(vPrevYearFinalQtr)"}

  > + <

  [Milestone Num] = {22}

  ,[Activity Measure Code]={'TS-GRL'}

  ,[Activity Period Year]={"$(vPrevYear)"}

  > + <

  [Milestone Num] = {22}

  ,[Activity Measure Code]={'TS-TRPL'}

  ,[Activity Period Year Quarter Desc]={"$(vPrevYearFinalQtr)"}

  >

  }

  [Activity Value])

,'#,##0') *1

[amayuresh]

Remove [Activity Period Year]={"$(vPrevYear)"} from script, may be you are storing Multiple values in variable vPrevYear

[sfatoux72]

It's not clear.

You speak about Section Access and script, but you give us an expression.

Be more explicit, please

[Gysbert_Wassenaar]

Section access has nothing to do with set analysis. If you use section access to reduce the data available to a user then possibly there is no data for the other years. But perhaps you mean set analysis instead of section access. What you posted contains no real clues. I can't tell what the variables contain. I have not idea how your expression is used. I don't know if you're using period fields as dimensions as well in the object where you use your expression. If you need help then you'll have to create a small qlikview document that demonstrates the problem.

See here for how to create documents that don't contain confidention information anymore:

Preparing examples for Upload - Reduction and Data Scrambling

And if you don't trust scrambing then make up some example data.

[#5]

My apologies had a bad start to the day. It is Set Analysis please also find attached a reduced data version. What I have currently happening is when you choose one year in Goal 6 it shows you the last Quarter data but what I now need to do is if I pick 2 years I get an extra text box and want to show the max year on the right and the middle is previous year and the box on the left show previous - 1.

[sfatoux72]

Hi,

Before answer you, I found something wrong.

I selected 2014 and after 2015 and we could see that values for 2014 are not the same where 2014 is current year or previous year

    

It's just because mesure [Activity Measure Code]={'TS-APP1','TS-APP2','TS-APP3','TS-APP4','TS-TRP1','TS-TRP2','TS-TRP3','TS-TRP4'} are removed due to current selection of Activity Period Year.

You could find below your expression compacted (more clear) and corrected (for the red object above):

= num(

Sum({$<

  [Milestone Num] = {22}

  ,[Activity Measure Code]={'TS-APP1','TS-APP2','TS-APP3','TS-APP4','TS-TRP1','TS-TRP2','TS-TRP3','TS-TRP4'}

  ,[Activity Period Year Quarter Desc]={"$(vPrevYearFinalQtr)"}

  ,[Activity Period Year]={"$(vPrevYear)"}

  > + <

  [Milestone Num] = {22}

  ,[Activity Measure Code]={'TS-GRR1','TS-GRR2','TS-GRR3','TS-GRR4'}

  ,[Activity Period Year]={"$(vPrevYear)"}

  > } [Activity Value]) ,'#,##0') *1

I let you modify the two other objects accordingly.

[Gysbert_Wassenaar]

I don't have to time to dig through your document and fix everything. Start with this:

If you select multiple Activity Period Year values then using =[Activity Period Year] as definition for the variable vYear will result in vYear having a null value since there are multiple possible values. So you need to be specific and tell Qlikview which value you want. I assume you want the maximum of the selected values: vYear: =max([Activity Period Year]).

I hope this helps you getting some steps towards the result you want.