Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
sourchandra
Contributor II
Contributor II

Calculate measure in a table at a particular dimension level ignoring the report filter

I have the following scenario(a simulation) where I am creating a table report with the following dimensions & a measure.The measure calculates the value fine at the dimension as expected but once the Filter(F) gets applied it brings in the measure value corresponding to the F for all the rows of the Dim1 .I want to calculate the measure at the Dim1(D1) level ignoring the filter .I cannot ignore totally the filter on this table by using alternate states as D1 should gets sliced by the F values but only that M should give values corresponding to D1 after F is applied on the table whch means M should ignore the filter once the D1 is filtered.So we are first applying the filter & then ignoring it while calculating the measure

Filter(F)          
           
Dim1(D1)Dim2(D2)Dim3(D3)Dim4(D4)Dim5(D5)Measure(M)     
           
1234    100(6)     
1235    100(8)     
1236    100(9)     
           
For dimension tagged to F the value of Measure is 100( that only gets reflected in Measure for different D1s)
The actual values of D1 used  without the filter are in brackets like 6…8  etc    
I would like the values like 6…8 to appear against the Dim1 with the filters on.   
           
2 Replies
Digvijay_Singh

What behavior you see if you disregard F filter in M measure using {<F=>} in the set expression?

sourchandra
Contributor II
Contributor II
Author

@Digvijay_Singh : I used the following as you suggested
Sum({<F=>} Num(M,'##.00')) but it is saying Ok in the edit expression window but once "Apply" button is pressed it gives invalid dimension

sourchandra_0-1630421286127.png

Also tried an AGGR function on top the Sum to aggregate on D1 level 

AGGR(Sum({<F=>} Num([M],'##.00')),D1)  but it is not bypassing the F & giving same values as earlier

Also tried :

Aggr(Sum({1<D1=P(D1)>} Num([M],'##.00')),D1) but it is adding up all the D1 .

 

Pls suggest.