Issue with if statement used in bar chart measure

[amr_qlik]

The below if statement is part of a larger calculation used to generate a bar chart.    The bar chart is not supposed change when the Product Number filter is used, but should change which product numbers are on the bar chart (it's a top 10) if any other filter is used.  However, when I select a Product Number in the filter every row that has the extra 0.08 from the if statement below loses that 0.08 on its bar height (unless it was the product number that was filtered on.  What I basically need is a way to hold the output of the below equation in place and not let it be adjusted by any filters.

if((count({<[Product Number]>}distinct{$ <[Fiscal Year] = {$(=MAX([Fiscal Year]))}>} [Product Category])
>count({<[Product Number]>}distinct{ <[Fiscal Year] = {$(=MIN([Fiscal Year]))}>} [Product Category])) and
MAX([Fiscal Year])> MIN([Fiscal Year]), '0.08', '0')

[Digvijay_Singh]

Not sure if I understand it properly but looks like you would need to disregard Product number from all the aggregated functions - 

if((count({<[Product Number]>}distinct{$ <[Fiscal Year] = {$(=MAX({<[Product Number]>}[Fiscal Year]))}>} [Product Number])
>count({<[Product Number]>}distinct{ <[Fiscal Year] = {$(=MIN({<[Product Number]>}[Fiscal Year]))}>} [Product Number])) and
MAX({<[Product Number]>}[Fiscal Year])> MIN({<[Product Number]>}[Fiscal Year]), '0.08', '0')

[amr_qlik]

I realize I didn't copy over the correct equation.  I've fixed my original above, but it should read 

if((count({<[Product Number]>}distinct{$ <[Fiscal Year] = {$(=MAX([Fiscal Year]))}>} [Product Category])
>count({<[Product Number]>}distinct{ <[Fiscal Year] = {$(=MIN([Fiscal Year]))}>} [Product Category])) and
MAX([Fiscal Year])> MIN([Fiscal Year]), '0.08', '0')

For additional context, the if statement is supposed to take a given Product Category and then find the oldest and newest fiscal years that Product Category has, and, assuming the two years aren't the same year (i.e. there's more than one row of data) then the number of times that Product Category code appears is counted for the two years, respectively.  Then the final counts are compared and 0.08 or 0 is selected based on that comparison. 

I went ahead and used your equation but swapped out Product Number for Product Category for the two  Product Numbers outside the {}'s but unfortunately that doesn't seem to work.  

[Digvijay_Singh]

Do you have a calculated dimension? I observed sometimes filters are not disregarded just by fixing the measures only, in case you got calculated dimension.

[amr_qlik]

No, I've only been using measures.  Is there a way to write all or part of this as a dimension?

[Digvijay_Singh]

Normally we avoid calculated dimension as far as its possible, if possible can you share a sample app to further drill down the issue? 

[amr_qlik]

I don't appear to have the permissions to download an app and share it.  However, I've built up a sample dataset that simulates my issue.  

Data:

LOAD * INLINE [

Product Number, Product Category, Fiscal Year

1, 1, 2020

2, 2, 2021

3, 3, 2020

1, 4, 2021

2, 5, 2020

3, 6, 2020

1, 7, 2020

2, 8, 2021

];

All you'll need from there is the measure we've been discussing above then just make a simple table with Product Number and the measure, plus a Product Number filter and you'll be at the same point as I am.  I'm trying to get it to a point where I can select a product number through a filter and  despite filtering all product numbers remain on the table, plus the value for product 2 stays at 0.08 while the other 2 stay at 0.

[amr_qlik]

Alternatively, is there a way create a field in the script with the same output as the if statement I'm using?

[Digvijay_Singh · Accepted solution]

My bad, your sample helped to find out the error in the expression, try this - 

if(
count(distinct{<[Product Number],[Fiscal Year]={$(=MAX({<[Product Number]>}[Fiscal Year]))}>} [Product Category])>count(distinct{ <[Product Number],[Fiscal Year]={$(=MIN({<[Product Number]>}[Fiscal Year]))}>} [Product Category])
and MAX({<[Product Number]>}[Fiscal Year])> MIN({<[Product Number]>}[Fiscal Year]),
'0.08', '0'
)

[amr_qlik]

Thank you!  This is exactly what I needed.