topic Re: Do multiple operations in one single expression in QlikView

Do multiple operations in one single expression

Wed, 25 Nov 2020 16:16:04 GMT

I have a table that I have loaded initialy in my script. Now I have a text object and I would like to assign it an expression based on the calculation from some table fields. I have a field that represents the type of hardware equipment and I have another field that represents its costs. something like below:

HW Equipment Cost

A1 1000 €

B3 550 €

Z8 2000 €

V3 1200 €

A1 650 €

A1 400 €

Z8 250 €

V3 100 €

I would like to calculate the following for each equipment between two dates:

(end date - initial date) / number of failures

note that:

1) end date - initial date corresponds to the user selection, she/he selects two dates, initial and final, and once selected, data is selected between these dates [initial date, end date] in days.

2) number of failures in this case is the same as the number of records, for example, for A1 is 3, B3 is 1, Z8 is 2, V3 is 3.

and finally once I have calculated the above expression for each equipment I would like to sum all them and divide between the number of equipments, in this case 4 equipments.

Then I want to assign this final calculation to one text object.

So how can I calculate the expression for this text object but without using scripts? How can I do it using an expression? Is it possible to iterate for each equipment, do the calculation for each of them, then sum all them and finally divide between the number of equipments in one single expression? I am not sure if i can do an iteration in an expression.

Re: Do multiple operations in one single expression

maxgro — Mon, 15 Aug 2016 19:50:56 GMT

sum(aggr(count([HW Equipment]), [HW Equipment]))

count(DISTINCT [HW Equipment])

9 / 4

Re: Do multiple operations in one single expression

swuehl — Mon, 15 Aug 2016 19:54:41 GMT

You somewhere lost me when talking about start and end dates and showing equipment and cost.

I think you can use advanced aggregation to get what you want, something like

=Sum( Aggr( (Max(EndDate) - Min(InitialDate) ) / Count(HWEquipment), HWEquipment))

/ Count(DISTINCT HWEquipment)

AGGR...

Pitfalls of the Aggr function

Re: Do multiple operations in one single expression

Mon, 15 Aug 2016 20:01:28 GMT

very sorry, I have edited the post. Initial and end dates are the dates user selects, that is, user selects one initial date and one final date. Then data is filtered between this two dates, and the result (end date - initial date) should be in days.

Very sorry for not to be clear initialy, now I have explained it.