Only if condition - Only 'x' but not 'y' or 'z' expression

[Hariprasath2394]

I am trying to show values only that is in 2020 year but not in the year 2018 or 2019.

Example, Below shows the customer, year, and Sales. I want to show the Customer and Sales value only if it is in year 2020 but not in 2018 or 2019.

Customer	Year	Sales
Italy	2018	1000
Italy	2019	500
Italy	2020	200
Germany	2019	600
Germany	2020	700
Spain	2020	400
France	2020	300

Expected output:

Customer	Year	Sales
Spain	2020	400
France	2020	300

I tried giving below expression in calculated dimension for customer value:

If (year = '2020' and year <> '2018' and year <>'2019', customer, null())

and given the year and sales in expression but it is not working as it gives all the 2020 value. I need to show the value only if it in year 2020 but not in 2018 or 2019. Can anyone please tell me the expression that I can give to show the value ''only if 2020 but not if 2018 or 2019''?

[edwin]

try this:

=sum({<Year={2020}, Customer=e({<Year={2019, 2018}>}Customer)>}Sales)

e() excludes customers with sales in years 2018, 2019

[Hariprasath2394]

Hi Edwin, Thanks for the response. Do you mean I want to write this expression in Calculated Dimension and add year and sales in Expression?

[sunny_talwar]

@Hariprasath2394 - I think what @edwin  is that use Customer and Year as dimension and his expression as a measure in your chart

[Hariprasath2394]

Hi @sunny_talwar thank you for your response. I tried the solution given by @edwin but unfortunately it is showing me all the customers that are in year 2020. 

Example,

Customer	Year	Sales
Italy	2020	200
Germany	2020	700
Spain	2020	400
France	2020	300

But I want to show only Spain and France as they are our new customers that has sales only in 2020. I don't want to show Italy and Germany in 2020 as they are not our new customers as they have value in 2018 and 2019. Thank you

[sunny_talwar · Accepted solution]

@Hariprasath2394 Tried it in QlikView, seems to be working without any issues

[edwin]

i used your data and i was only getting Spain and France.  do you have other expressions in your chart?

[edwin · Accepted solution]

take a look at this

[Hariprasath2394]

Thank you @sunny_talwar the expression that you have written is working for me know.

Thank you @edwin =sum({<Year={2020}, Customer=e({<Year={2019, 2018}>}Customer)>}Sales) since the customer is added in the expression after the 2019, 2018, it gave me all the 2020 values. But @sunny_talwar has eliminated that and it was working fine.

Thanks a lot to both of you @edwin  @sunny_talwar  for helping me 😊

[sunny_talwar]

@Hariprasath2394 - Really adding Customer caused that issue? I thought having a Customer or not having it doesn't make any difference at all. Which version of QlikView or Qlik Sense are you using?