Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)))
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.
May be something like this:
If(APP_DATE > Max(TOTAL {<ACT_AMT = {'>0'}>} APP_DATE), FY_QTR)
It would be nicer if you
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.
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?
Ya less then (<) sign make sense, if that's what you questions is
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.