topic Re: Need Set Analysis Fixed For Certain Dimensions and Dynamic Others On Visual in QlikView

Need Set Analysis Fixed For Certain Dimensions and Dynamic Others On Visual

lenny500 — Sat, 16 Nov 2024 00:54:00 GMT

Hello All,

New to QLIK and love it so far...this is prob an easy question.

I have 3 dimensions (SCENARIO, VERSION and CATEGORY)

For the 'SC.Actual' SCENARIO, I need the VERSION to be fixed with 'VR.Final', however i want the CATEGORY to be flexible depending on what the user selects on the Filter Pane Chart.

Here is my set analysis expression:

Sum({1<SCENARIO_ANCESTOR_ID={'SC.Actual'},VERSION_ID={'VR.Final'}>}AMOUNT)

this stays the same number no matter what category is chosen on the filter pane chart. is it possible for some dimensions to be set and some to be dynamic?

Thanks!

Re: Need Set Analysis Fixed For Certain Dimensions and Dynamic Others On Visual

tresB — Mon, 06 Apr 2020 05:33:12 GMT

Try like:

If( SCENARIO_ANCESTOR_ID='SC.Actual', Sum({<VERSION_ID={'VR.Final'}>}AMOUNT) , Sum(AMOUNT) )

Re: Need Set Analysis Fixed For Certain Dimensions and Dynamic Others On Visual

lenny500 — Thu, 09 Apr 2020 21:15:17 GMT

Thanks so much for quick feedback!

I need the Version to stay "VR.Final" even if the VERSION is change on the filter pane chart. It's a transpose extensions and here is the complete code that i am using:

IF(ValueList('Actual','CV','OL')='Actual',
Sum({1<MEASURE_ANCESTOR_ID={'AC.IS40'},SCENARIO_ANCESTOR_ID={'SC.Actual'},VERSION_ID={'VR.Final'}>}AMOUNT)
,

IF(ValueList('Actual','CV','OL')='CV',
Sum({<LAYER_DESC_LVL_2={'Total Layer'},MEASURE_ANCESTOR_ID={'AC.IS40'},SCENARIO_DESCRIPTION={'Budget'}>}AMOUNT)
,

IF(ValueList('Actual','CV','OL')='OL',
Sum({<LAYER_DESC_LVL_2={'Total Layer'},MEASURE_ANCESTOR_ID={'AC.IS40'},SCENARIO_DESCRIPTION={'Outlook'}>}AMOUNT)

)))

Re: Need Set Analysis Fixed For Certain Dimensions and Dynamic Others On Visual

lenny500 — Thu, 09 Apr 2020 21:14:25 GMT

Sorry did I just complicate it? usually i get a response pretty quickly...

Re: Need Set Analysis Fixed For Certain Dimensions and Dynamic Others On Visual

Brett_Bleess — Wed, 29 Apr 2020 13:34:50 GMT

Believe the following Design Blog post should get you on the right track with things:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett