14 Replies Latest reply: Jan 16, 2017 3:56 AM by Yeo Poh sai

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

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

my qvf

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

Can you provide data in excel file?

also explain what you need as final output..

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

Hi Sir

Enclosed simple of my raw data. and the report i need as below. ( Below only can display all 2015 year ) in case company with 2016 sales it cannot display.

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

1) I don't have QlikSense installed in my PC so can't check your file

2) Can you provide simple excel file with few lines of sample data and the final output table you are looking for.

3) Try to reduce the size of the file whenever you upload in your question. 8MB file is too big to download and work.

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

Hi,

What I observe with two expression is that they both are same, correct me if i am wrong.

Whereas so far what i understand your problem, if you want to show cy as 2016 and ly as 2015 then you need to modify the expression.

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

Hi,

I think you can use the if statement on your measures like stated below:

LY will get the value of CY if LY is 0

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

CY will get the value of LY if CY is 0

CY = if(Sum({<Year = {\$(=max(Year))}>}sales) = 0,Column(1),Sum({<Year = {\$(=max(Year))}>}sales) )

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

Hi Buela

I think your approach should work , using if.

I think the field name you use sales is wrong , it should be REVENUE_C

But I try to use your 2 expression LY and CY , but i get same value for Both  LY and CY.

By the way when i use your expression , i need to select year = 2016 in order to display the value.

Paul

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

Hi Paul,

Please see attached qvf for the changes I've made on the 3rd sheet.

the year field I used is "Year" which is not available on your data.
I've modified the set analysis by changing "Year" to "year" field as dimension.

Yes you still need to select the year 2016 if you want to display 2016 and 2015, if there is no selection the default years will be 2017 & 2016

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

Hi Sir

Thank you very much , I will test again.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

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

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

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

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?

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

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

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

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.

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

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