Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
am fairly new to Qlik Sense and have a problem, where I didn't find a solution in the Qlik Help and Community section.
Below an example table of my data, I'm doing reporting for most recent date and need a table where I just highlight items that are new:
Year | Fruit | Value |
2022 | Apple | 4 |
2022 | Banana | 9 |
2022 | Lemon | 2 |
2023 | Apple | 5 |
2023 | Banana | 6 |
2023 | Lemon | 2 |
2023 | Orange | 3 |
Resulting table of new items in Qlik Sense should just show the recent value for Orange as this is the only fruit in 2023 that didn't exist in 2022:
Orange | 3 |
I figured out how to calculate change in value etc., but not how to see what's null in previous period.
Thanks for your help and apologies for raising rookie questions.
Dan
for dimension 'Fruit', use the below, in a straight table.
=if(isnull(aggr(only({<Fruit={"=count({<Year={'$(=max({1}Year)-1)'}>}Value)"}>}Fruit),Fruit)),Fruit)
this thread could be of help as well.
Either you have a datefield or a year field i guess like in this case there is year field and i am taking this for illustration:-
Try this:-
aggr(Only({< Fruit=e({1<Year={"$(=Max(Year)-1)"}>})>}Fruit),Fruit)
Also i have used 1 in the set expression of exclude, this is to make sure that filters doesnt work on it, but as in your case i think that making the set expression free from effects of Calendar Fields Should do the work and you can try that.
for dimension 'Fruit', use the below, in a straight table.
=if(isnull(aggr(only({<Fruit={"=count({<Year={'$(=max({1}Year)-1)'}>}Value)"}>}Fruit),Fruit)),Fruit)
this thread could be of help as well.
Either you have a datefield or a year field i guess like in this case there is year field and i am taking this for illustration:-
Try this:-
aggr(Only({< Fruit=e({1<Year={"$(=Max(Year)-1)"}>})>}Fruit),Fruit)
Also i have used 1 in the set expression of exclude, this is to make sure that filters doesnt work on it, but as in your case i think that making the set expression free from effects of Calendar Fields Should do the work and you can try that.
Thank you both, Akash & G3S, your solutions worked and have helped me a lot! Coming from SQL and Business Objects it requires different way of thinking to narrow down sets of data.
Regards, Dan