Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

1 Solution

Accepted Solutions
Not applicable

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.

View solution in original post

14 Replies
paulyeo11
Master
Master
Author

my qvf

MK_QSL
MVP
MVP

Can you provide data in excel file?

also explain what you need as final output..

paulyeo11
Master
Master
Author

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.

report by competitor.png

MK_QSL
MVP
MVP

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.

miskin_m
Partner - Creator
Partner - Creator

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.

Not applicable

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) )

paulyeo11
Master
Master
Author

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

Not applicable

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

paulyeo11
Master
Master
Author

Hi Sir

Thank you very much , I will test again.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804