Filter by dimension

[Saryk]

I'd like to add a filter by dimension instead of by value ; say I have field A {1, 2, 3} and field B {4, 5, 6} that correspond for example to number of sales for employee A and employee B or each product.

I want a pie chart with sales per product, so I have a dimension that is "Product name" and a measure that is Sum(A) + Sum(B).

If I add a filter item (the last one in graphs, I have Qliksense in French which doesn't help, sorry for mistranslations), I can only add a Dimension which then shows all values to filter by Value. 

I want to be able to select A or B to show their individual sales on the same chart, how can I filter this ?

Reality is more complex than that but the analogy simplifies things for explanation purposes.

[Saryk]

Indeed that was it. I hoped Match() wouldn't match "B" to "A without B"...

Now there is no difference between "A without B" alone and "A without B" and "B" both selected, I have to find a way to change that.

[cpomeren003]

Ok, I can help with that aswell

I changed the first one so it should work, but I would advise to just change every option to something like this
(Option "B" changed, and option "A without B" still using the old method):

=if(GetSelectedCount(Options) = 0, Sum(SaleProductA + SaleProductB),
    Sum(
    	if(
        	WildMatch(GetFieldSelections(Options), 'B') = 1
        	or WildMatch(GetFieldSelections(Options), '*B,*')
        	or WildMatch(GetFieldSelections(Options), '*, B')
        	, SaleProductA, 0
          )
        +
        if(
        	WildMatch(GetFieldSelections(Options), '*A without B*') = 1
            , SaleProductB, 0
          )
       )
   )

 Hope it helps!

[Saryk]

I changed "B" to "B1" and that solves it I think ; and I'm quite annoyed because everything works fine in the test file here ; and I have the exact same code in my real file - just different names but exactly the same format, - and it doesn't work there.

"A without B" and "B1" don't add up to "A" in my file, but they do in the test file... I don't get it. 

Having 

Sum(SaleProductA + SaleProductB)

doesn't work for me on my real file, by the way, even though it does on the test file...

😩 

[Saryk]

Woops, here's the latest version ; I was changing and testing things while typing the answer.

[Saryk]

Ok, so I have NULL valuesin my data (they show as [ - ]) which is probably why 

Sum(SaleProductAwB + SaleProductB)

doesn't work while 

Sum(SaleProductAwB) + Sum(SaleProduct(B)

 does.

Could this be the issue ?

I load my data from an XLSX file, is there a way to replace the [ - ] fields with 0 ?

[cpomeren003]

Here is the solution, basically it comes down to: we should have used RangeSum instead of Sum.

See attached document, should work as expected:

[Saryk]

I changed my loading script and I now use 

if(IsNull(B), 0, B) as B

 And it solves the 

Sum( AwB + B )

thing.

AwB + B still not equal to A though, I keep looking ! 

[cpomeren003 · Accepted solution]

Oops, attached wrong document, second try.

[Saryk]

Yeaaaah, that doesn't work  at all 😕

It should on paper, but the results are ridiculously wrong...

I don't get what's so weird about my data. 
I'll keep digging.

[Saryk]

OMG I found it !

Say I have 2 types of sales, some are counted in "A" (because they're unimportant), some are more detailed between "A without B" and "B" (because the difference matters).

I hadn't filtered for those, so I had discrepancies between "AwB" + "B" and "A" because the detail for "AwB" and "B" is only there if it's an important sale. 

I was comparing apples and oranges. 

Problem solved, thanks !