How to include null value in a expression

Thu, 15 Sep 2011 05:50:07 GMT

Hi Gurus, I have a pivot chart with 2 dimensions CUSTOMER, WEEKNUMBER and an expression SUM(SALES_BOXES), something like this:

CUSTOMER	WEEK NUMBER	20	21	22	23	24	25	26	27	28	29	30	31	32
21085		0	0	40	-20	0	0	0	0	0	0	0	0	0
21086		0	0	50	89	0	103	0	0	0	310	0	0	0
21088		0	0	0	72	26	6	0	0	47	101	2	0	115
21080		0	0	13	0	11	0	20	0	0	0	66	20	62
21081		0	12	0	22	22	0	10	21	0	11	30	0	10
21082		0	18	0	5	20	0	26	25	26	0	20	11	0
21083		33	-18	0	25	0	32	0	0	15	0	0	45	0

I want to calculate Fractile 0.75 each customer but I have an issue if I compare the percentile value (fractile) in MS Excel is not the same value.

For Example the customer 21088 in excel the result is 47but in qlikview is 86.5, I think Excel is using all the weeks not nulls and nulls (13) but qlikview is just using NOT NULL (7). This is the expression:

fractile(AGGR(SUM(SALES_BOXES),CUSTOMER,WEEKNUMBER),0.75)

Qlikview values

CUSTOMER	Percentil 75
21085	25,0
21086	103,0
21088	86,5
21080	51,5
21081	22,0
21082	25,3
21083	32,8

Excel values:

CUSTOMER	PERCENTILE75
21085	0,00
21086	50,00
21088	47,00
21080	20,00
21081	21,00
21082	20,00
21083	25,00

How can I include null values in the expression? I need to get all the weeks.

Thanks in advance,

Juan/

topic How to include null value in a expression in QlikView

How to include null value in a expression