2 Replies Latest reply: Dec 16, 2015 12:10 PM by Peter Cammaert

# More SET Expression TOGETHER

HI,

Can we write if condition in SET analysis

=if(Match(Particulars_No,3,4,5),FABS(if(Particulars_No = 5,FABS(Above([Prev Prev Month YTD],1) + Above([Prev Prev Month YTD],2)),

if(Particulars_No = 15,(Above([Prev Prev Month YTD],1) + Above([Prev Prev Month YTD],2) + Above([Prev Prev Month YTD],3) + Above([Prev Prev Month YTD],4) +  Above([Prev Prev Month YTD],5) +  Above([Prev Prev Month YTD],6) +  Above([Prev Prev Month YTD],7)),

if(Particulars_No = 17,(Above([Prev Prev Month YTD],12) - Above([Prev Prev Month YTD],2)),

if(Particulars_No = 20,(Above([Prev Prev Month YTD],1) - Above([Prev Prev Month YTD],3)),

if(Particulars_No = 23,(Above([Prev Prev Month YTD],1) - Above([Prev Prev Month YTD],3)),

if(Particulars_No = 29,(Above([Prev Prev Month YTD],1) + Above([Prev Prev Month YTD],2) + Above([Prev Prev Month YTD],3) + Above([Prev Prev Month YTD],4) +  Above([Prev Prev Month YTD],5)),

if(Particulars_No = 30,(Above([Prev Prev Month YTD],7) - (Above([Prev Prev Month YTD],1))),

SUM({<FYear = {\$(vMaxYear)},FIN_MNTH_NUM = {\$(vOctMonth)},Particulars=,Particulars_No=,Notes=>} (OPENING + (TRANSACTIONS))))))))))),

if(Particulars_No = 5, FABS(Above([Prev Prev Month YTD],1) + Above([Prev Prev Month YTD],2)),

if(Particulars_No = 15,(Above([Prev Prev Month YTD],1) + Above([Prev Prev Month YTD],2) + Above([Prev Prev Month YTD],3) + Above([Prev Prev Month YTD],4) +  Above([Prev Prev Month YTD],5) +  Above([Prev Prev Month YTD],6) +  Above([Prev Prev Month YTD],7)),

if(Particulars_No = 17,(Above([Prev Prev Month YTD],12) - Above([Prev Prev Month YTD],2)),

if(Particulars_No = 20,(Above([Prev Prev Month YTD],1) + Above([Prev Prev Month YTD],3)),

if(Particulars_No = 23,(Above([Prev Prev Month YTD],1) + Above([Prev Prev Month YTD],3)),

if(Particulars_No = 29,(Above([Prev Prev Month YTD],1) + Above([Prev Prev Month YTD],2) + Above([Prev Prev Month YTD],3) + Above([Prev Prev Month YTD],4) +  Above([Prev Prev Month YTD],5)),

if(Particulars_No = 30,(Above([Prev Prev Month YTD],7) - (Above([Prev Prev Month YTD],1))),

SUM({<FYear = {\$(vMaxYear)},FIN_MNTH_NUM = {\$(vOctMonth)},Particulars=,Particulars_No=,Notes=>} (OPENING + (TRANSACTIONS)))))))))))

• ###### Re: More SET Expression TOGETHER

I doubt it.

Set Analysis is used to change the set of values you stuff into an expression, and not the expression itself. I may have lost count somwhere but I detected at least 16 different result calculations in this one expression.

• ###### Re: More SET Expression TOGETHER

There may be other solutions that could simplify this code though.

In case you use this expression in a table: is Particulars_No an external value (that will not change when calculating each successive row) or is it part of the table or associated with a dimension field?

Peter