topic Re: Display Data for a Certain Amount of Time - Quartiles in QlikView

Quartiles by Using an IF Statement

Tue, 12 Mar 2013 16:25:44 GMT

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

Re: Display Data for a Certain Amount of Time - Quartiles

sushil353 — Wed, 13 Mar 2013 12:01:55 GMT

Please post sample data or sample app

Re: Display Data for a Certain Amount of Time - Quartiles

Wed, 13 Mar 2013 12:10:24 GMT

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

Re: Display Data for a Certain Amount of Time - Quartiles

Mon, 18 Mar 2013 11:58:52 GMT

Changed the expression to:

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

if(

(

EOY < 7

AND

month(DATE) < EOY + 7

AND

month(DATE) > EOY + 3

)

(

EOY = 7

AND

month(DATE) > 10

month(DATE) = 1

)

(

EOY = 8

AND

month(DATE) < 3

month(DATE) = 12

)

(

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?

Re: Display Data for a Certain Amount of Time - Quartiles

Thu, 21 Mar 2013 09:47:45 GMT

Anyone have any ideas about this?

Re: Display Data for a Certain Amount of Time - Quartiles

Wed, 24 Apr 2013 11:23:31 GMT

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