topic Re: Conditional Values in QlikView

Conditional Values

kevinstanfield — Wed, 30 Mar 2016 09:28:15 GMT

I'm struggling to find a way to achieve a method of applying a "calculated condition" within a object. I've used quotes around "calculated condition" as I'm not certain that I'm describing this correctly so I'll describe what I'm trying to achieve below.

I'm updating a Telecoms dashboard where I have an expression "Average Revenue Per User (ARPU)" (Revenue Column) that is sorted by the top highest ARPUs and another expression that counts the number of users invoiced- the dimensions being month and the tariff that the ARPU applies to - see example below:

Date	Tariff	Revenue	Subs Invoiced
		£16.	123,456
Feb-2016	Tariff 1	£75.	1
Feb-2016	Tariff 2	£46.	10
Feb-2016	Tariff 3	£40.	3
Feb-2016	Tariff 4	£40.	16
Feb-2016	Tariff 5	£38.	1
Feb-2016	Tariff 6	£34.	7
Feb-2016	Tariff 7	£33.	7
Feb-2016	Tariff 8	£33.	4
Feb-2016	Tariff 9	£32.	1
Feb-2016	Tariff 10	£30.	21,179
Jan-2016	Tariff 1	£1,332.	1
Jan-2016	Tariff 2	£700.	1
Jan-2016	Tariff 3	£291.	3
Jan-2016	Tariff 4	£98.	1
Jan-2016	Tariff 5	£84.	1
Jan-2016	Tariff 6	£81.	53
Jan-2016	Tariff 7	£79.	16
Jan-2016	Tariff 8	£74.	81
Jan-2016	Tariff 9	£68.	1
Jan-2016	Tariff 10	£67.	3

The

The reason for wishing to apply a condition is that where only small number of users are invoiced on a particular tariff this is of no real interest - example is Feb-16 Tariff 1 the ARPU figure is £75 but only 1 user invoiced (subs invoiced column).

What I'd like to do is to say that there must be a minimum number of users invoiced to appear in the top 10 for the month. so in layman's terms I'd be saying Subs Invoiced > 1000 as the condition. I've tried lots of different ways to achieve this but can't get it to work. Any thoughts on how to achieve this?

As a "by the way" does anyone know how not to display totals in the table - I can only find the option of totals as the top or bottom row?

Many thanks in advance.

Kevin

Re: Conditional Values

Gysbert_Wassenaar — Wed, 30 Mar 2016 09:31:32 GMT

You could sort on Revenue * Subs to get around it.

Re: Conditional Values

kevinstanfield — Wed, 30 Mar 2016 09:44:04 GMT

Interesting suggestion Gysbert - thanks.

My view is that I wouldn't get the result I'm after if I took this option - For example if there were 100,000 Subs invoiced on Tariff 2 at £10 ARPU then this would potentially mask the purpose of the analysis in that I want to show the best ARPU for tariffs with a certain number of subs invoiced e.g. Tariff 3, 15,000 subs Invoiced at £45 ARPU and Tariff 4, 1,200 Subs Invoiced at £27 ARPU.

Kind regards

Kevin

Re: Conditional Values

Gysbert_Wassenaar — Wed, 30 Mar 2016 09:48:04 GMT

Ok, what's the key field? You have months and tarifs. Is there a single field with values that correspond to the month-tarif combinations? If you've got that you can try something like avg({<MonthTarifKey={"=sum([Subs invoiced])>10000"}>}Revenue)

Re: Conditional Values

kevinstanfield — Wed, 30 Mar 2016 09:59:09 GMT

Thanks Gysbert - I could certainly create that field via the load script so I'll give that a try and get back to you.