Separating Chart Expression into Smaller Functions

[jashinskyjm]

I have an expression for a measure in a chart that is working properly; however, it is becoming lengthy and a little difficult to follow its logic on first glance:

if(sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose)=0, 0,
     (if((sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose))<5,
          '*',
          sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose))
     )
)

The code "sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose)" appears three times in my expression. Is it possible to break up the code in to something like the following?

FUNCTION=sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose)

if(FUNCTION=0, 0,
     (if((FUNCTION)<5,
          '*',
          FUNCTION)
     )
)

[Anil_Babu_Samineni]

Off course, You can create as variable and call that into measure like in script or simply from Variable overview 

Script:

LET FUNCTION='=sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose)';

OR

Variable Overview:

sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose)

And then your expression should be

if($(FUNCTION)=0, 0, (if($(FUNCTION)<5, '*', $(FUNCTION))))

[jashinskyjm]

Thank you for responding so quickly.

When I implemented the code you gave for the variable viewer, the chart displays the some of all counts across all time periods in each quarter. The original code breaks down the counts by each quarter. I included some example results below to illustrate:

Original code outputs:

Q1 = 125

Q2 = 150

Q3 = 100

Q4=200

New Code outputs:

Q1 = 575

Q2 = 575

Q3 = 575

Q4 = 575

Also, if it's put in the load script or the variable viewer, is it still able to adapt to selections that are made?

[Anil_Babu_Samineni]

Aggregate your final expression into dimension wise as per your chart, Example

Sum(Aggr(if($(FUNCTION)=0, 0, (if($(FUNCTION)<5, '*', $(FUNCTION)))), Dim1, Dim2)

[ArnadoSandoval]

Hi @jashinskyjm 

Yes you can, there is a little trick when you create the variable in the Variable Overview:

This is the expression you want to assign to a variable:

sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose)

This expression already contains SET analysis elements, something to keep in mind when you create a variable with this expression, the expression must start with an equal sign,

=sum(drug_overdose) + 0*sum({1<year_incidence={$(=concat(distinct year_incidence,','))}>} drug_overdose)

Without the leading = sign in the expression, it will not work as you expect in the UI, see the screenshot of my test QVF illustrating this fact.

Hope this helps,