Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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!
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...
😩
Woops, here's the latest version ; I was changing and testing things while typing the answer.
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 ?
Here is the solution, basically it comes down to: we should have used RangeSum instead of Sum.
See attached document, should work as expected:
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 !
Oops, attached wrong document, second try.
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.
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 !