Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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''?
@Hariprasath2394 Tried it in QlikView, seems to be working without any issues
try this:
=sum({<Year={2020}, Customer=e({<Year={2019, 2018}>}Customer)>}Sales)
e() excludes customers with sales in years 2018, 2019
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?
@Hariprasath2394 - I think what @edwin is that use Customer and Year as dimension and his expression as a measure in your chart
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
@Hariprasath2394 Tried it in QlikView, seems to be working without any issues
i used your data and i was only getting Spain and France. do you have other expressions in your chart?
take a look at this
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 😊
@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?