Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

kevinstanfield
New Contributor III

Conditional Values

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:

   

DateTariffRevenueSubs Invoiced
£16.123,456
Feb-2016Tariff 1£75.1
Feb-2016Tariff 2£46.10
Feb-2016Tariff 3£40.3
Feb-2016Tariff 4£40.16
Feb-2016Tariff 5£38.1
Feb-2016Tariff 6£34.7
Feb-2016Tariff 7£33.7
Feb-2016Tariff 8£33.4
Feb-2016Tariff 9£32.1
Feb-2016Tariff 10£30.21,179
Jan-2016Tariff 1£1,332.1
Jan-2016Tariff 2£700.1
Jan-2016Tariff 3£291.3
Jan-2016Tariff 4£98.1
Jan-2016Tariff 5£84.1
Jan-2016Tariff 6£81.53
Jan-2016Tariff 7£79.16
Jan-2016Tariff 8£74.81
Jan-2016Tariff 9£68.1
Jan-2016Tariff 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

4 Replies

Re: Conditional Values

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


talk is cheap, supply exceeds demand
kevinstanfield
New Contributor III

Re: Conditional Values

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

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)


talk is cheap, supply exceeds demand
kevinstanfield
New Contributor III

Re: Conditional Values

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

Community Browser