6 Replies Latest reply: Mar 15, 2016 12:13 PM by Gysbert Wassenaar RSS

    Section Access

    James Whitmore

      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

        • Re: Section Access
          Mayuresh Athalye

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

          • Re: Section Access
            Sébastien Fatoux

            It's not clear.

             

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

             

            Be more explicit, please

            • Re: Section Access
              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.

                • Re: Section Access
                  James Whitmore

                  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.

                    • Re: Section Access
                      Sébastien Fatoux

                      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

                      2016-03-15 16_43_26-QlikView x64 - [C__Users_sfatoux_Downloads_Reduced Data.qvw_].png     2016-03-15 16_45_26-QlikView x64 - [C__Users_sfatoux_Downloads_Reduced Data.qvw_].png

                      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.

                      • Re: Section Access
                        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.