topic Re: If Statement for Dimension in QlikView

If Statement for Dimension

cbaqir — Tue, 01 Mar 2016 20:01:30 GMT

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)))

Re: If Statement for Dimension

sunny_talwar — Tue, 01 Mar 2016 20:10:59 GMT

May be something like this:

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

Re: If Statement for Dimension

Clever_Anjos — Tue, 01 Mar 2016 20:12:21 GMT

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

Re: If Statement for Dimension

swuehl — Tue, 01 Mar 2016 20:12:32 GMT

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.

Re: If Statement for Dimension

cbaqir — Tue, 01 Mar 2016 20:20:46 GMT

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?

Re: If Statement for Dimension

sunny_talwar — Tue, 01 Mar 2016 20:24:05 GMT

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

Re: If Statement for Dimension

swuehl — Tue, 01 Mar 2016 20:52:22 GMT

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.