topic Re: Expression total using aggr() and SUM() in QlikView

Expression total using aggr() and SUM()

poluvidyasagar — Sat, 16 Nov 2024 04:18:21 GMT

Hi,

I am trying to calculate the total number of orders who average time is less than 5 hours (300 minutes). However, it does not get reflected in expression total in straight box.

So, i have a straight table like the following: It has no dimensions but only measures:

Avg Time	No of orders	Orders < 5 hours
1880	8	0 (need help here)

The column "Orders <5 hours" is incorrect. Because i do have orders with avg time less than 5 hours.

This is the other straight table with order number as dimension. In this table, the column under 5 hours gives me right values but does not give sum in the expression total.

OrderNumber	Time	Under 5 hours
	1880 (expression total)	0 (expression total) Need help here
1128365	108	1
1128366	768	0
1128368	768	0
1128369	11988	0
1129280	22	1
1129281	1192	0
1129284	22	1
1129285	172	1

These 2 straight tables can be seen in then file attached. have attached the qlikview file for reference.

can someone please help me on this?

Thanks,
Vidya

Re: Expression total using aggr() and SUM()

tincholiver — Thu, 28 Feb 2019 19:05:29 GMT

you need expression time in the second graph.

I don´t know if it´s the result that you have looking for.

Re: Expression total using aggr() and SUM()

poluvidyasagar — Thu, 28 Feb 2019 19:20:55 GMT

Hi,

I need two things here:

1.In the first straight table with "order number" as dimensions, i want expression total in the column "Under 5 hours"

If you see from the straight table with order number as dimensions, if you sum the column values of "under 5 hours", it should be 4 which should be expression total. ( it is not currently shown)

2. In the second straight table, i would like to have the same value in the column " under 5 hours" but with no dimension. So, basically aggregating it by dimension.

Let me know if you understood my questions.

Thanks,

Vidya

Re: Expression total using aggr() and SUM()

tincholiver — Thu, 28 Feb 2019 22:14:13 GMT

Hey Vidja,

I could only solve the first case of the sum of the columns but not the other.
the problem is that the aggr function to get the calculation to remove the dimension loses meaning, then you have to modify the expression.
Can you explain how you get the hours of the orders that take more than one day? and how do you drive on weekends?

do this in the expression of the graph and you will have the sum of the values

Re: Expression total using aggr() and SUM()

tincholiver — Fri, 01 Mar 2019 00:38:12 GMT

Vidja,

can solve what you wanted even though I made some modifications in the script to be able to solve it more easily.
I do not know if it will serve you as I did but the result is the same
tell me if it served you
regards.

Re: Expression total using aggr() and SUM()

veidlburkhard — Fri, 01 Mar 2019 10:22:55 GMT

Hi Poluvidyasagar,

for your second question the answer is

Sum(Aggr(Only(If(Time/60 <= 5, 1, 0)), OrderNumber))

Happy Qliking

Burkhard

Re: Expression total using aggr() and SUM()

poluvidyasagar — Fri, 01 Mar 2019 12:40:37 GMT

Hi Veidlburkhard,

This expression does not give me the results i need. It still gives me zero.

Thanks,
Vidya

Re: Expression total using aggr() and SUM()

poluvidyasagar — Fri, 01 Mar 2019 12:47:39 GMT

Hi Tincholiver,

I know this feature using "Sum" in total mode. However, i would this to be calculated using total expression.

Because what works for question 1 should work for question 2.

You have mentioned that " aggr function to get the calculation to remove the dimension loses meaning". Can you elaborate on this?

Thanks,
Vidya

Re: Expression total using aggr() and SUM()

poluvidyasagar — Fri, 01 Mar 2019 12:50:46 GMT

Hi Tincholiver,

The data provided in the problem is simple. But, I have complex data which needs lot of set analysis. I have not provided those details here.

I am sure we can solve the problem the way u did but i would like this to be done on the chart script.

Thanks,
Vidya

Re: Expression total using aggr() and SUM()

tincholiver — Fri, 01 Mar 2019 14:10:46 GMT

I was referring to that in the Time expression, the number of orders will result in 4 having dimension to OrderNumber, if you remove that dimension the result is 1880, then the expression = if ([Time] <300,1,0) is working well only that it does not find any value that fulfills that condition that's why it gives zero