Quartiles by Using an IF Statement

[#1]

Hi,

I have a straight table which displays date, IM, and another calculated expression.

There is also a field loaded called EOY (month) and another one EOY_NO (month number).

I need to display the four highest IM for each yearly quartile which depends on EOY. So if EOY field is March, the highest value has to be displayed for a quartile after March which would be April, May and June. EOY is different for each company that can be selected. Then 3 more values have to be displayed for the rest of quartiles.

Any suggestions?

I used this method which worked if EOY was the same for all the companies, e.g. for Quartile 2 (I had separate tables for each quartile):

if(month(DATE) > 2 and month(DATE) < 6), sum(IM))

[sushil353]

Please post sample data or sample app

[#3]

What it should look like:

Date	IM	NC	Ratio
Quarter 1 e.g. 26/02/2013	200	1000	=IM/NC
Quarter 2 e.g. 15/05/2013	300	1000
Quarter 3	500	1000
Quarter 4	200	1000
Current Date	200	1000

EOY - End of Year
5
12 - applies for the selected company

So lets say for the above example, Company A, EOY is December as can be seen from the table. This means that Q1 would include January, February and March. I need to display the highest IM amount for that quarter. I need to do this for every quarter.

EOY might be different for Company B, it might be March or any other month so automatically quarter months would be different.

Hope this makes sense

[#4]

Changed the expression to:

if

     (DATE > addmonths(today(),-12),

if(

(

     EOY < 7

     AND

     month(DATE) < EOY + 7

     AND

     month(DATE) > EOY + 3

      )

OR

      (

     EOY = 7

     AND

     month(DATE) > 10

     OR

     month(DATE) = 1

      )

OR

      (

     EOY = 8

     AND

     month(DATE) < 3

     OR

     month(DATE) = 12

      )

OR

      (

     EOY > 8

     AND

     month(DATE) < EOY - 5

     AND

     month(DATE) > EOY - 9

      ),          

sum(IM),

'no data')

)

This is for Q2, but it still shows 1 (Jan) data when EOY is 12 (Dec). It doesn't meet the conditions though to be displayed.

Is my statement correct? Why is Jan data displayed in Q2 results?

[#5]

Anyone have any ideas about this?

[#6 · Accepted solution]

Used variables for dates and multiplied by 0.25, 0.5 etc for each quartile