Set Analysis with Button Selection

[#1]

Hello,

I have the following set anaylysis formula: ({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>} qty) that looks at the current Period, for 2014 and excludes products 'others' and gives me 'total quantiites' this works just great. However on my report I have created two toggle buttons one for Units and one for Dollars (they are link with a 'vViewMode' expression or variable so the user can select what type of data to look on the report. I did this using an if formula and it allwork great. I want to use the same logic for Set analysis but I don't know how to incorporate the value for my buttons into the set analysis (using an if statement:

if(vViewMode='Units',if(Year=2014,if(PeriodID=vCurrentPeriodID,qty,amount). I would like to use set analysis because is more powerful and will allow me to have some extra functionality. On my sample with the 'if' statemnt I am able to reference PeriodID with my Variable/Expression, but I am not able to ref,erence anything with my buttons (Units/Dollars) because my data doesn't have a field called ViewMode or something I can reference.

I want to achieve the followign:

({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'} vViewMode={'Units'} >} qty) or else Amount)

is this possible? can anybody help me please?

thank you for your time,

Hugo

[swuehl]

Use a variable definition in variable overview for a variable vTest like

=if( vViewMode = 'Units', 'qty', 'Amount')

Then replace qty in your set expression with $(vTest)

edit: like

sum({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>} $(vTest))

[maxgro]

if I understand use 2 variable, vUnit and vDollar

and set it depending on button click so they will have the values

vUnit=1, vDollar=0

or

vUnit=0, vDollar=1

you new expression should be

({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>} qty) *  $(vUnit)

+

({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>} amount) *  $(vDollar)

[#4]

Hi Massimo thank you for your reply.

I am a bit confused, here is what I have: I have 2 buttons
(Units and Dollars) with the properties set as below.

If the user click on
Units, then I show [qty] if the user click on Dollars, then I show [Amount]

I used to have this scenario using an ‘IF’ statement but Iprefer to use Set Analysis due to the flexibility and it works better when userselects a particular Month or Year.

I currently have the following formula:

(Sum({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>}
qty)
+
Sum({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>}
Amount))

My current problem is that regardless if the user selects ‘Units’ or ‘Dollars’ my table shows only [qty] because I don’t know how to incorporate the button selection into the Set Analysis. if I were to use an if statement I could have as the last condition ….if(stype<>’Others’, if(vViewMode=’Units’,qty,Amount))). I

s it possible to accomplish what I want? I tried your sample but if I change the value of
Units and Dollars as you suggested then my other formulas don’t work anymorebecause they are set to check for the value to be ‘Units or Dollars’.

Thanks,

Hugo

[preminqlik]

Hi there , Have you tried in conditional enabling the expressions

write two expressions as shown below :

[maxgro · Accepted solution]

if('$(vViewMode)'='Units',

Sum({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>}qty

,

sum({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>} Amount

)

[#7]

Thank you Prem, your solution works, I only wish I could mark 2 answers as 'correct' ones because both answers are correct the only difference is one I use 2 formulas separate and the other one I use an if scenario that technically are two formulas combined in one.

[#8]

Thank you Massimo,

your formula works with a tiny modifification on the closing brackets )).

I just changed it to:

if('$(vViewMode)'='Units',
Sum({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>}qty)
,
sum({$<PeriodID ={$(vCurrentPeriodID)},Year={2014},stype-={'Others'}>}$(vNetAmount)*RateConverted))

I really appreciate your help and Prem's, both solutions work now I can move on with my project.

Hugo