topic Re: Qlik (Sense) is not using dimensions for calculation in App Development

Qlik (Sense) is not using dimensions for calculation

sirpod90 — Mon, 21 Nov 2016 14:56:15 GMT

Hello everybody,

I am running at the moment in really weird problem and can not come up with any conclusions.

My Data looks like that:

Container-Name	Action-Date	Step-Name
Container12	01.01.2016	StepName-1
Container12	02.01.2016	StepName-2
Container12	03.01.2016	StepName-3
Container12	04.01.2016	StepName-1-V
Container43	01.01.2016	StepName-A
Container43	01.01.2016	StepName-B

I want to have table with the max step name the container past in time

So my result should look like that:

Container-Name	Action-Date	Step-Name
Container12	04.01.2016	StepName-1-V
Container43	01.01.2016	StepName-B

I set up a table with Container-Name as dimension and AGGR(max(Action-Date), Container-Name) as second dimension. If I now add the fact MaxString(Step-Name) the result looks for max string in all containers and not with the maximum date.

So my result looks like that:

Container-Name	Action-Date	Step-Name
Container12	04.01.2016	StepName-3
Container43	01.01.2016	StepName-B

It seams like the fact column would ignore the two dimensions before hand?

Why?? Do I have a wrong understanding of dimensions and facts?

When I tried to solve the problem with set analysis and only the container as dimension:

maxstring({$<Action-Date= {'&(=max(Action-Date))'}>}StepName)

My result where right but just for one Container, so Qlik took the max-Date from all dates, ignoring the dimension again.

Container-Name

Step-Name

Container12

StepName-1-V

Container43

I also tried to combine the set analysis with AGGR but the result is still wrong.

Is there something I really miss or does Qlik have a problem?

I am thank full for any help.

Regards!

Re: Qlik (Sense) is not using dimensions for calculation

sunny_talwar — Mon, 21 Nov 2016 14:57:56 GMT

Try using 1 dimension and 2 expressions:

Dimension

Container_Name

Expression

Date(Max(Action-Date))

FirstedSortedValue(Step_Name, -Action-Date)

Update: Also checkout how FirstSortedValue() function work

Value Associated with Min/Max Value of Another Field (Front End Solution)

Re: Qlik (Sense) is not using dimensions for calculation

sirpod90 — Mon, 21 Nov 2016 15:18:20 GMT

This works if the container passed one Step-Name but if not the result is empty even so the date is unique

Re: Qlik (Sense) is not using dimensions for calculation

sunny_talwar — Mon, 21 Nov 2016 15:52:38 GMT

You can have multiple Step_Names for each date and container?

Re: Qlik (Sense) is not using dimensions for calculation

sirpod90 — Mon, 21 Nov 2016 16:14:53 GMT

Yeah I have multiple rows, that is the point i didn't realized!

It is possible to get the first sorted value ordered by 2 parameters, or do I have to use a set analysis?

Re: Qlik (Sense) is not using dimensions for calculation

sunny_talwar — Mon, 21 Nov 2016 16:24:49 GMT

Would you want to show all the steps for the highest date within a container or pick one of the steps from the list? If it is the later, how do you plan to pick one? MaxString(Step Name)?

Re: Qlik (Sense) is not using dimensions for calculation

sirpod90 — Mon, 21 Nov 2016 16:32:10 GMT

Container-Name	Action-Date	Step-Name	ID
Container12	01.01.2016	StepName-1	1
Container12	02.01.2016	StepName-2	2
Container12	03.01.2016	StepName-3	3
Container12	04.01.2016	StepName-1-V	8
Container43	01.01.2016	StepName-A	05
Container43	01.01.2016	StepName-B	06
Container12	05.01.2016	StepName-1-C	4
Container12	05.01.2016	StepName-1-D	5

I want to show one container step with the highest date and id. But the id is not in the right order, it is just relevant if i have more steps in the same time.

So my results should look like that:

Container-Name	Action-Date	Step-Name
Container43	01.01.2016	StepName-B
Container12	05.01.2016	StepName-1-D

Re: Qlik (Sense) is not using dimensions for calculation

sunny_talwar — Mon, 21 Nov 2016 16:33:59 GMT

May be this:

Dimension

Container_Name

Expression

Date(Max(Action-Date))

FirstedSortedValue(Step_Name, -(Action-Date + (ID/1E10)))

Re: Qlik (Sense) is not using dimensions for calculation

sirpod90 — Mon, 21 Nov 2016 16:38:20 GMT

Thank You.

Great and fast response

Re: Qlik (Sense) is not using dimensions for calculation

Digvijay_Singh — Mon, 21 Nov 2016 17:09:51 GMT

Hi Sunny,

Could you pl help to understand the 1E10 divisor, didn't get that. Thanks in advance.

Digvijay,

Re: Qlik (Sense) is not using dimensions for calculation

sirpod90 — Mon, 21 Nov 2016 17:16:07 GMT

It just means, that the ID is devided by 10 to the power 10

ID / 10000000000

So that the first parameter (Date/Time) weight more than the ID.