Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Remove [Activity Period Year]={"$(vPrevYear)"} from script, may be you are storing Multiple values in variable vPrevYear
It's not clear.
You speak about Section Access and script, but you give us an expression.
Be more explicit, please
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.
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.
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.
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.