Counting distinctive values in Pivot-table without flags (only frontend)

[Sapphire]

Hello community,

ok, so here is a question regarding a problem that, I know, should ideally be handeled in backend. However, only having the frontend means, this sadly gets tricky. But maybe one of you has a working solution, so let`s jump into it.

The Task:

I need to set up a pivot list with two colums and two rows that counts the distinctive values of ID numbers sorted to the respective colums/rows. They have to adhere to different conditions, though, so I set up an example for you to better understand.

Base Data:

ID	Manufacturer	Statustype	Status	Destination	Current Pos
1	BMW	A	Active	USA	Spot A
2	VW	A	Inactive	EU	Spot B
3	KIA	A	Active	Asia	Spot C
3	KIA	B	Inactive	Asia	Spot C
4	VW	C	Active	USA	Spot B
5	BMW	B	Inactive	EU	Spot A
5	BMW	D	Inactive	EU	Spot A
6	KIA	-	-	Asia	Spot A

The following conditions should be adhered to:

-Structure: All IDs should be counted (distinctively, only one count per ID) in a Pivot table with rows sorted by Manufacturer and coums sorted by Destinations, which should be segmented in Status "Active" and "Inactive".

- Every ID should be counted exactly once, IF they are in one or more specifit "current Pos" (so basically a simple filter, which is easily set up - just listed for completeness)

- If one ID has at least one Status "Active" (does not matter, which one), it should only be counted as "Status active"

- If One ID with has no Status "Active", and only with Status "Inactive" or null, it should be only counted as Status "Inactive"

So here, for example, the expected Output, if I want to see all IDs that are on either Spot A, B or C:

	Destination	USA		EU		ASIA
Manufacturer	Status	Inactive	Active	Inactive	Active	Inactive	Active
BMW		0	1	1	0	0	0
KIA		0	0	0	0	1	1
VW		0	1	1	0	0	0

The Problem:

Whereas the structure of the pivot is not a problem to set up, the Issue is, that ID can and will be counted multiple times, if they have both one Active and one Inactive Status.

I already tried

- using distinct counting methods in both the Measure-field (which is ID) and the Status-field.

- using the Status field as a measure field instead.

- using two measure fields to manually split up Status Active and Inactive.

However so far, I could not find a solution to solve this problem in frontend.

I know there should be solutions with flags and other means in backend, to give me better Data as a basis for this pivot in the first place, however, for this App I sadly have no means to get to or change the backend/loading script, so (very unluckily) I need to solve this one in frontend.

Anyone having an idea with this unholy task?

Faithful regards,

Sapphire

[marcus_sommer]

I think you couldn't solve the task with a single aggregation else you will need nested aggregations because your relevant data are distributed on multiple records. This may go in a direction like:

sum(aggr(if(wildmatch(concat(Status, ','), '*Active*'), 1, 0), Manufacturer))

[Sapphire]

Hey Marcus_sommer!

Thanks for your reply. I tried to convert your expression into my Qlik but almost exclusively get zeros. In the mean time, however, I also managed to get a workaround with two expressions that will, in a default table, show me for each distinct ID either if it has at least one active status ("1" column 1) or not ("1" in column 2).

I  built a pivot in NPrinting with that for a "quick and dirty" solution, however it would be better to get the pivot set up in Qlik Sense directly. And here the Issue remains that - as far as I understand it - I need to get this two expressions into one. However: If I do that, basically the pivot contains an expression giving out a "0" or "1" (alternatively "Active","Inactive" or whatever) for each distinct ID. Sadly, the pivot does not know now how to display these outputs as to put it in two different columns (or rows, alternatively).

I also tried to use the expressions that work in the default table as two seperate measures in the Pivot. However, that does not work, if I don`t put the Status as a column. And If I put it as a column, everything gets just gets doubled, which isn`t exaclty pretty, either. So I currently am out of Ideas now. However It seems the main problem I have is located in the set-up of the Pivot using the expression I have built - or rather the pivot not knowing how to display the result of said expressions.

The expressions I built so far are:

// Counting ID that have at least one Status Active:
Count({<[Status]={'Active'} >} distinct [ID])

//Counting ID that have no Status Active:
if( Count({<[Status]={'Inactive'} >} distinct [ID])>0, null(), Count(distinct [ID]))

 Maybe this might help?

Faithful regards,

Sapphire

[marcus_sommer]

Indeed my suggestion was just to use a single column to show active & inactive because such 0/1 output could be very readable displayed in this way and using two columns is a wasting of screen. Also the result must not be just 0/1 else with a pick-wrapping other values could be shown, for example:

pick(Expression + 1, dual('inactive', 0), dual('active', 1))

[Sapphire]

Ok maybe I don't get my head around this expression, but as far as I see it that is a translation from 0/1 to "inactive/"active", am I right so far?

Basically, to put my expressions together into one I get:

if( Count({<[Status]={'Active'} >} distinct [ID])>0, 0, 1)

however I also can go with

if( Count({<[Status]={'Active'} >} distinct [ID])>0, 0'Inactive','Active').

But how can I get this Expression into the pivot so that it gets displayed correctly? Or how do I merge it with your expression to achieve it? I tried several ways already but somehow I'm at a loss so far. I know I miss something, but I don't know, what.

[marcus_sommer]

Yes, the pick() is just a kind of translation of the intended 0/1 results. But like above mentioned it will be important to apply a logic which counts against multiple records which may an aggr() or also a TOTAL statement. So it may in the end look like:

pick(
sign(sum(aggr(if(wildmatch(concat(Status, ','), '*Active*'), 1, 0), Manufacturer))) + 1,
dual('inactive', 0), dual('active', 1))

which returned the active/inactive results within a single column. If you really want to show it in two columns you need to use two expressions - a bit reduced and the second one reversed to the first one.

[Sapphire]

Ok I think I see the problem now. So basically, the expression itself works, it basically puts everything in one column in a default table. However, If I try to use this expression in a pivot, it still shows me this:

Or, if I put in the Status as a column in the pivot:

(used my extraordinary powerpoint-skills for clarifications of the dimension names)

Since I can't direclty access the column in a default table (if I could, my expectation would be that the pivot would count/sum correctly) but instead have to put in the expression into the value calculation of the pivot itself, the pivot seems to struggle.

Basically I still think the right way to solve this is to put this expression into the loading script in the first place to then be able to access the resulting column for the pivot. Which sadly still isn't possible, hence the issue with finding the way only using frontend means. But I hope we're getting, there 🙂

[marcus_sommer · Accepted solution]

That certain information/structures doesn't exists within the data-model does not mean that they shouldn't created there. All essential logic belonged to the data-model and should not be created within the UI because the efforts are usually much higher (if any possible) and often the performance suffers.

Therefore I suggest you creates an appropriate task that the data-model is adjusted. Especially as it looked that not all data-associations are right because Audi get a 1 in total but 0 in USA and NULL in Europe - which means the causing 1 record has no value within the destination.

Beside of this you may create a calculated dimension with something like: valuelist('active', 'inactive') and within the expression you queries the values, like:

if(valuelist('active', 'inactive'), 'active', expr1, expr2)

[Sapphire]

I totally agree with you, the right way is to adjust the data model. I just hoped there maybe were are kind of "easy" solution in frontend as well. So I guess it is time for me to pass the task over to another desk within my organisation who is authorised to change the model. For the time being my workaround will hold, though, so we'll make due until the adjustment has happened.

Still, I want to thank you for all your input. Despite not exactly solving the issue (but rather: getting to the point of realising that the solution does not lie in the UI but in adjusting the data model itself) I did learn a lot new about how to tackle other problems that definitely will come my way in the future  🙂

Sincere regards,

Sapphire