New clients - No turnover last 2 years

[Georg022]

Hello,

I am new at qlikview and I have a problem I can't solve...

I have a table diagramm with client name, and columns for turnover 2017,2018,2019

If there is turnover in 2019, but NOT in 2017 and 2018 (last 2 years), I want to mark them in the new column with "new client". I can't get it right.

Here is what I have so far:

if(SAP_Rechnungsdatum_Jahr='2017' AND '2018' AND (if(SAP_Nettowert=0), 'New client', 'old client')

SAP_Rechnungdatum_Jahr is the year

SAP_Nettowert is the turnover

Is there any chance you can help me?

Have a nice day

George

[miskinmaz]

count({<[Customer Code]-=
{'=count( aggr(count({<[Fiscal Year]={"<$(=max([Fiscal Year]))"}>} distinct [Customer Code]),[Customer Code]))<>0'}>}distinct [Customer Code])

you can try to modify the above code to get the status

[Georg022]

Thank you very much. I got the code running, but it didn't delivery the correct result.  I want to check if there was turnover iin the last 2 years, and with the code I got only negative hits, because there always was something in the past... 

[miskinmaz]

you have to modify the code to meet your requirnment

({<[Fiscal Year]={"<$(=max([Fiscal Year]))"}>}  this give you customer with no previous sales .

{<[Fiscal Year]={">=$(=max([Fiscal Year])-2)<=$(=max([Fiscal Year]))"}>} for customer with no sales in past two years. 

[Georg022]

Thank you for your help. I am sorry, but it still doesnt work, I think I did something wrong.

I edited the code to this:

count({<[SAP_Auftraggeber_BIPID]-={'=count( aggr(count({<[SAP_Rechnungsdatum_Jahr]={"<$(=max([SAP_Rechnungsdatum_Jahr])-1)<=$(=max([SAP_Rechnungsdatum_Jahr]))"}>} distinct [SAP_Auftraggeber_BIPID]),[SAP_Auftraggeber_BIPID]))<>0'}>}distinct [SAP_Auftraggeber_BIPID])

I always get "1" as result, in every line of my table diagramm. I uploaded a picture to show you the result.

Thank you

Georg

[miskinmaz]

Please find the attached sample file for the expression

[Georg022]

Thank you, I will try 🙂

[Brett_Bleess]

Georg, have a look at the following links, they will give you all the options etc., hopefully if you are still trying to get things sorted, you can find something there to help you figure out what is missing.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Sorry I do not have anything better for you, I noticed the other poster left you a qvf file which is not going to open in QlikView either, just FYI, that is a Sense app, apologies for that.

Regards,
Brett

[Georg022 · Accepted solution]

Thank you very much for your help 🙂

First I was confused, but I found out that the file was for Qliksense and I could extract the code. But it didn't work. Finally I used this simple code and I got the result I needed:

if(sum({<SAP_year={'2018'}>}SAP_amount)=0 and sum({<SAP_year={'2017'}>}SAP_amount)=0 and sum({<SAP_year={'2019'}>}SAP_amount)>0, 'new client')

But I also will check out your links.

Have a nice day

Georg