Show which values appeared in last year that don't appear this year

[#1]

Hi Guys,

I've seen this before but can't figure out what it was or how to do it, that's when you smart folks come in I am reviewing our next financial years Q1 forecast and what I would like to see is if any of the budget holders forgot anything. One way of achieving this is to use last years actuals and see if any line item descriptions don't appear in their Q1 forecasts.

The dimension I'm working with is

• Fiscal Set - This will contain the structure I want to compare, so Q1FiscalSet and then say LastYearFiscalSet
• Period - This for Q1

The conditions that I want flagged in the values that should populate should be the following

1. If there is a value in LastYearFiscalSet but not in Q1FiscalSet then highlight red
2. If there is a value in Q1FiscalSet but not in LastYearFiscalSet then highlight green

So the first dimension being the structure, should contain all the values that appear in Q1FiscalSet and LastYearFiscalSet

Would i use set analysis where i remember seeing a syntax that allows you to view what doesn't exist in a selection or conditionally just concatenate the structure

Hope you all understand and I haven't overly complicated the question

Thanks in advance, I know you got this

Cheers,

Byron

[matt_crowther · Accepted solution]

Slightly cowboy solution attached using a fron-end solution using the following expression:

=if(Year=2012,if(index(concat(total distinct if(Year=2011,Dim),','),Dim)>0,'Present','Missing'),if(index(concat(total distinct if(Year=2012,Dim),','),Dim)>0,'Present','Missing'))

This should provide a foundation for what you require.

All the best,

Matt

@QlikviewBI

[#3]

I thought i already marked this yes None-the-less thank you very much for your help, it was exactly what i was looking for

Cheers,

Byron