Solved: Re: How to make QV display the max year sales ? As... - Qlik Community

paulyeo11 · ‎2017-01-12

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

Report Inappropriate Content · ‎2017-01-15

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

paulyeo11 · ‎2017-01-12

my qvf

MK_QSL · ‎2017-01-12

Can you provide data in excel file?

also explain what you need as final output..

paulyeo11 · ‎2017-01-12

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.

MK_QSL · ‎2017-01-12

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 · ‎2017-01-12

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.

Report Inappropriate Content · ‎2017-01-12

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 · ‎2017-01-12

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

Report Inappropriate Content · ‎2017-01-12

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 · ‎2017-01-12

Hi Sir

Thank you very much , I will test again.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +659:261804

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