How to make QV display the max year sales ? As My customer sales some not all of them have 2015 & 2016 sales

[paulyeo11]

Hi All

This expression return 2015 sales

Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"} >} REVENUE_C/1000000

)

This expression return 2016 sales

Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"} >} REVENUE_C/1000000

)

My data some only have 2016 sales , all of them have 2015 sales.

How to make the above expression take 2015 sales when 2016 no sales ?

How to make the above expression take 2016 sales when 2016 have sales ?

So that i can display both.

Now only TDS and ADL have 2016 data. Other only have 2015 sales data.

Paul

[paulyeo11]

Hi Sir

Now the issue if i compare the growth rate , many company 0% , any way to show the % ? As my main aim to show competitor growth rate.

Paul

[#3]

Hi

Based on your data you only have 2 data revenue for 2016 competitor.

Therefore we will be having 0% growth for the rest of the competitor which has the same revenue for LY(2015).

Do you need to display 100% for those competitors who has equal value for LY and CY revenue?

[paulyeo11]

Hi Buela

what i need is how to make all competitor have sales on 2016.

if competitor have 2016 sales do nothing,

if competitor don't have sales on 2016, move 2015 sales to 2016 and 2014 sales to 2015.

Last time what i did is i adjust all competitor sales manually to 2015 have sales. meaning if they don't have sales in 2015 , i move their sales from 2014 to 2015. then i can get competitor growth rate.

This make my life hard as it is hard to maintenance,

I am not sure i am able to make use of year_n ? not sure will it make all competitor sales have sales in CY for year_n = 1 , and LY for year_n = 2 ?

Any idea ? Hope i not confuse you.

Paul

[#5 · Accepted solution]

Hi,

You can change the if statement for LY

from:

LY = if(Sum({<year = {$(=max(year )-1)}>}sales) = 0,Column(2),Sum({<year = {$(=max(year )-1)}>}sales) )

to:

LY = if(Sum({<year = {$(=max(year )-1)}>}sales) = 0,Sum({<year = {$(=max(year )-2)}>}sales) ,Sum({<year = {$(=max(year)-1)}>}sales) )

But the problem is that how about if the year after last year will also be 0.

I think it is much better to create a separate table on where it will check the latest revenue data then mark it as CY year Revenue.

[paulyeo11]

Hi Buela

I just try your new suggested expression , it does not help,

So i end up manually adjust all the competitor latest year sales to 2015. ( Since i only have 2 competitor company the sales 2016. So i change these 2 company sales from 2016 to 2015 , and 2015 to 2014 and so on ).

Now working fine. even though this is the best solution.

Thank you very much.

Paul