Expression only on those values for a dimension which are present in both years

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a straight table showing difference in sales and change in rank for two years for different categories.

The dimension is Categories(There are around 9 different categories) and there are two expression

Expression 1:To calculate difference between sales of two year for the categories

(It also calculates difference in Transactions based on selection of Field Metric)

Expression 2: To calculate change in rank for different categories in two years.

Expression 1 :

=if(GetFieldSelections(Metric) = 'Sales',if(GetFieldSelections(Quarter),

num((sum({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}, Type = {"DT"}>} Sales) -

sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}, Type = {"DT"}>} Sales)),'€#,##0;€#,##0'),

num((sum({<Financial_Year = {'$(Selected_Year)'}, Type = {"DT"}>} Sales) -

sum({<Financial_Year = {'$(Previous_Year)'}, Type = {"DT"}>} Sales)),'€#,##0;€#,##0')),

if(GetFieldSelections(Quarter),

num((count({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}, Type = {"DT"}>} TXN) -

count({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}, Type = {"DT"}>} TXN)),'#,##0'),

num((count({<Financial_Year = {'$(Selected_Year)'}, Type = {"DT"}>} TXN) -

count({<Financial_Year = {'$(Previous_Year)'}, Type = {"DT"}>} TXN)),'#,##0'))

Expression 2 :

if(GetFieldSelections(Metric) = 'ToV',if(GetFieldSelections(Quarter),

Rank(sum({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}, Type = {"DT"}>} Sales)) -

Rank(sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}, Type = {"DT"}>} Sales)),

Rank(sum({<Financial_Year = {'$(Selected_Year)'}, Type = {"DT"}>} Sales)) -

Rank(sum({<Financial_Year = {'$(Previous_Year)'}, Type = {"DT"}>} Sales))),

if(GetFieldSelections(Quarter),

Rank(count({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}, Type = {"DT"}>} TXN)) -

Rank(count({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}, Type = {"DT"}>} TXN)),

Rank(count({<Financial_Year = {'$(Selected_Year)'}, Type = {"DT"}>} TXN)) -

Rank(count({<Financial_Year = {'$(Previous_Year)'}, Type = {"DT"}>} TXN))))

The problem I am facing:

In my straight table I get all the values of dimension Categories.Whether they are present in the selected year or not.The categories which are not present in the both year(There are 9 categories but few of them do not appear in selected year and previous year as well)

I want to show only those values of dimension in my straight table which are present in both year to calculate and exclude all others.

How can I put a condition where in I can say that evaluate the above expression only for those dimension values which are present in Selected Year and Previous Year

Attached is the screen shot of my straight table.In that I want to display only those categories which are present in both years(category 1 and 3)

topic Expression only on those values for a dimension which are present in both years in QlikView

Expression only on those values for a dimension which are present in both years