topic Re: Percentile analysis by Dimension value - Set analysis in QlikView

Percentile analysis by Dimension value - Set analysis

Fri, 15 May 2015 20:04:54 GMT

I would like to create an expression as stated in the attached document.

The percentile has to be calculated based on value column only if Model = ModelA

Dimensions			Expression
Product ID	Model	Value	Required expression	Comments
2222	ModelA	0.05	0%	Need to calculate percentile only for ModelA
2223	ModelA	0.06	33%
2224	ModelA	0.07	67%
2225	ModelA	0.08	100%
2226	ModelB	0.2	Other
2227	ModelB	0.23	Other
2228	ModelB	0.24	Other
2229	ModelB	0.25	Other
2230	ModelB	0.23	Other
2231	ModelC	0.5	Other
2232	ModelC	0.53	Other
2233	ModelC	0.6	Other
2234	ModelC	0.7	Other
2235	ModelC	0.83	Other
2236	ModelC	0.88	Other

Re: Percentile analysis by Dimension value - Set analysis

Sat, 16 May 2015 00:15:35 GMT

I'm going to take a guess that you'd like the expression to display the cummulative % if Model=ModelA and "other" in any other case. Here's my shot. Tried not using the full accumulation feature as that can break down with more complicated situations. !

Re: Percentile analysis by Dimension value - Set analysis

Mon, 18 May 2015 17:04:13 GMT

Thank you Jesse, I would like to know if it is a requirement to have the model column sorted the way it is.

Re: Percentile analysis by Dimension value - Set analysis

Mon, 18 May 2015 17:44:37 GMT

Hi Prakash,

Since the expression utilizes the above() function, the answer is yes. I am not sure of another way to do that expression without "full accumulation", which would also require sorting. Perhaps another user here could chime in?

Re: Percentile analysis by Dimension value - Set analysis

Mon, 18 May 2015 17:46:45 GMT

Thank you Jesse for the quick reply.

In my use case I would need an expression without Above function as I cannot sort my table. The prototype I created does not capture that. Thanks again

Re: Percentile analysis by Dimension value - Set analysis

Mon, 18 May 2015 18:53:53 GMT

Hi Prakash,

Sorry I wasn't able to help. Actually, it looks like Model is a field you CAN sort on. I'm seeing that regardless of whether I sort ascending or descending, it still works

Re: Percentile analysis by Dimension value - Set analysis

Tue, 19 May 2015 21:26:28 GMT

No worries Jesse. I have more expressions in the same structure in my actual data set and sorting will not be an option. I really appreciate your help in attempting to make this work.