topic Re: Calculate and Assign Decile on load in QlikView

Calculate and Assign Decile on load

Thu, 30 Jul 2015 12:05:42 GMT

Hi There

I have a dataset which contains customers and their associated profit value.

I am trying to create a decile view of this data by assigning each customer a decile value during the load script.

Whilst researching this problem in the QlikView Community I have managed to reuse a calculated dimension suggested for a similar problem. The expression is formatted like this;

=IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.1), 'Decile01',

IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.2), 'Decile02',

IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.3), 'Decile03',

...etc

....and this works for charts and tables and dynamic interaction (well almost - the distribution isnt exactly in 10% chunks but close).

However, I want to able able to assign the Decile value\name as a fixed attribute for each customer during the load process so I'd have a new column called "DecileNumber" or similar. Using a calculated dimension in the QVW layer doesn't give me the a fixed value that I can work with.

My real data set is quite large (i.e. several million customers).

Any suggestions appreciated!

Re: Calculate and Assign Decile on load

Kushal_Chawda — Thu, 30 Jul 2015 12:18:07 GMT

Have you tried?

Load customer,

F(PROFIT <= FRACTILE(TOTAL PROFIT, 0.1), 'Decile01',

IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.2), 'Decile02',

IF(PROFIT <= FRACTILE(TOTAL PROFIT, 0.3), 'Decile03'))) as Decile

From table

group by customer

Re: Calculate and Assign Decile on load

Anonymous — Thu, 30 Jul 2015 12:34:25 GMT

Hi Jason,

for script side load see attached example

You will get idea how to do this.

you need to modify the expression as required

Thanks

BKC

Re: Calculate and Assign Decile on load

Thu, 30 Jul 2015 13:52:33 GMT

Thank you for the response. When I use the suggested script I get a failure on load;

Aggregation expression required by Group by clause

Re: Calculate and Assign Decile on load

Thu, 30 Jul 2015 14:37:38 GMT

Hi There

Thank you. I have tried this and it almost works but I can't get the nested IF statement to work.

Do you have any ideas on what the syntax should be?

Thanks

Re: Calculate and Assign Decile on load

Anonymous — Mon, 03 Aug 2015 05:35:11 GMT

Can you explain if you are getting error on your nested If or FRACTILE() function is causing the issue ?

Thanks

BKC

Re: Calculate and Assign Decile on load

Mon, 03 Aug 2015 09:54:30 GMT

Hi Balkumar

I am working with your example QVW and I have updated example to include 10 customers (A to K).

In your exampke you commented that I needed to "Input my IF Statement here" but I have got stuck trying expand the IF statement in your example QVW by using IF statements applied to the vTotalSales variable that you created. When that didnt work I also tried using the FRACTILE statements which I applied to TOTAL Sales.

The attached text file shows the 4 examples I have tried (commented) and their outcomes. None of these worked.

Based on these 10 example customers and the way which I have applied the example sales value, the desired outcome would be for each customer to be in 1 of the deciles. Of course my "real" problem data is customer and profit but if I can get your example QVW to display what I need then I can adapt to my real problem.

Thanks