Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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...
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.
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
Please find the attached sample file for the expression
Thank you, I will try 🙂
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
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
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