If Statement for Dimension

[cbaqir]

Right now I have a dimension like this: if(today()>APP_DATE,FY_QTR)

but I want it to only display the FY_QTR if sum(ACT_AMT)>0 for the end of the quarter. Something close to this  maybe?

=if(APP_DATE>$(=Max({<ACT_AMT={'>0'},FY_QTR)))

[sunny_talwar]

May be something like this:

If(APP_DATE > Max(TOTAL {<ACT_AMT = {'>0'}>} APP_DATE), FY_QTR)

[Clever_Anjos]

It would be nicer if you

• Use only FY_QTR as dimension
• Create your first expression with your logic, returning 0 or 1
• Set "Dimension Limits" to narrow your records when 1st expression = 1

[swuehl]

You need to use advanced aggregation if you want to use aggregation functions per dimension value, maybe something like

=Aggr( If( Today() > APP_DATE and Sum(ACT_AMT) >0, FY_QTR), APP_DATE)

Not sure if this is what you want, just to demonstrate the general approch using aggr() function.

[cbaqir]

Had some more clarification... I have a variable called vPriorQuarter = date(QuarterEnd(addmonths(max(FCST_DATE),-3)), 'MM-DD-YYYY') .

I only want to show the quarter in the graph for quarters that have completed - not during the current quarter or future quarters. Right now, for example, I would only expect to see FY16 Q2 and earlier since FY16 Q3 doesn't end until 3/31/16.

Is =If(APP_DATE < Max(TOTAL {<ACT_AMT = {'>0'}>} APP_DATE), FY_QTR) not the right way to go?

[sunny_talwar]

Ya less then (<) sign make sense, if that's what you questions is

[swuehl · Accepted solution]

Well, it's hard to answer your questions for me, seems I miss context every time I try to understand what you are requesting.

If you want to limit your APP_DATE values to previous FY_QTRs, maybe like

If(APP_DATE < vPriorQuarter, FY_QTR)

but here,

If(FY_QTR < vPriorQuarter, FY_QTR)

could work also. And maybe just a dimension FY_QTR and a set analysis filter in your expressions would be better anyway.

And if you want to combine both filter, maybe like

If(APP_DATE < vPriorQuarter and APP_DATE < Max(TOTAL {<ACT_AMT = {'>0'}>} APP_DATE) , FY_QTR)

but again, not sure what you are trying to achieve at all.