How to create an "and" & "or" expression?

[Anonymous]

Hi,

I have a field called FYQTR that has values as the following: FY2016-Q1

The idea is to display values from last 3 quarters, I was using this expression:

=Count({$<YR={2016}, QTR={">01"} >} DISTINCT AutoID)

however as now we are on a quarter 1, then it won't display the first quarter of 2017, is there a way that I can display the top 3 quarters using the maxstring function. To automatically display the highest 3 quarters.

Please let me know and thank you in advanced for your help.

[Clever_Anjos]

Which other fields your Calendar has?

[marcus_sommer]

The easiest way would be to create a quarter-counter maybe per:

autonumber(FYQTR) as QTRCounter

and then within the set analysis:

=Count({$<QTRCounter={">=$(max(QTRCounter)-2)"} >} DISTINCT AutoID)

the matching will happen on a pure numeric level and there would be no trouble with the year-shift.

- Marcus

[swuehl]

If you want to follow Marcus' idea, I think there is an equal sign missing in the dollar sign expansion to force QV to evaluate the aggregation:

=Count({$<QTRCounter={">=$(=max(QTRCounter)-2)"} >} DISTINCT AutoID)

The approach is also discussed in more detail (including the potential need to clear selections in other calendar fields) here:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

[Anonymous]

Where should I put the autonumber?

[marcus_sommer]

Autonumber(FYQTR) as QTRCounter should be if possible created within the master-calendar. If you don't use one yet it would be considerable to add this kind of dimension-table to your datamodel. Here you will find many informations to this topic: How to use - Master-Calendar and Date-Values.

Of course it's also possible to create this field within a fact-table and also to use an expression inside from the autonumber, like: autonumber(YearExpression & QuarterExpression).

- Marcus

[jonathandienst]

And of course the AutoNumber() must be evaluated in date order to create a useful sequence number. This should be no problem if you are using a master calendar. But if you are getting the QTRCounter field from the fact table, the values may be unordered, then you could use

Mid(FYQTR, 3, 4) * 4 + Right(FYQTR, 1) as QTRCounter,

or

(Mid(FYQTR, 3, 4) - 2000) * 4 + Right(FYQTR, 1) as QTRCounter,

[Anonymous]

Ok, this is a little complex to me. Can I add the Mid(FYQTR, 3, 4) * 4 + Right(FYQTR, 1) as QTRCounter in the expression? or should I add it in the script and then call the QTRCounter?

Can you please help me with the expression that I should use and where to declare it?

[jonathandienst]

In your load script, when loading either the master calendar or the fact table (depending on where you load FYQTR).

[Anonymous]

Can I just add Mid(FYQTR, 3, 4) * 4 + Right(FYQTR, 1) as QTRCounter, in the Load and then I can use the QTRCounter condition?