topic How filter on equipement whose data volume has not changed over 3 consecutives months? in App Development

How filter on equipement whose data volume has not changed over 3 consecutives months?

Waïl_Riachi_Sanofi — Thu, 08 Oct 2020 07:17:55 GMT

Hello, I would like to know how we could filter, for example, on equipment whose data volume has not changed over 3 consecutive months, or on equipment which has had an increase in data volume greater than 10 GB during the last trimestre?

Re: How filter on equipement whose data volume has not changed over 3 consecutives months?

Lauri — Fri, 09 Oct 2020 20:04:34 GMT

If you will always filter on the same timeframe (3 months), you could create a dimension in the load script showing the change in data volume over 3 months (or simply 'yes/no' it changed).

If you want flexibility to filter on different timeframes, you could create a dimension in the app using Aggr, and filter on that. Something like:

=Aggr(sum({<Date={">=$(=MonthStart(AddMonths(Today(), -$(vMonth1)))<=$(=MonthEnd(AddMonths(Today(), -$(vMonth1)))"}>} DataVolume) - sum({<Date={">=$(=MonthStart(AddMonths(Today(), -$(vMonth2)))<=$(=MonthEnd(AddMonths(Today(), -$(vMonth2)))"}>} DataVolume), EquipmentID)

And create input boxes for the two variables for how many months ago.

This gets you the change between the 2 months. You could enclose in an IF to see if the calculation equals zero.

Re: How filter on equipement whose data volume has not changed over 3 consecutives months?

Waïl_Riachi_Sanofi — Mon, 19 Oct 2020 09:32:41 GMT

Hello @Lauri , I didn't understand your formula, what does it correspond to? What does vMonth1 and 2 correspond to? How to do for the "yes / no" if the volume of the equipment has changed or not? Thank you in advance

Re: How filter on equipement whose data volume has not changed over 3 consecutives months?

Waïl_Riachi_Sanofi — Mon, 19 Oct 2020 09:37:20 GMT

A sample of my data,

Re: How filter on equipement whose data volume has not changed over 3 consecutives months?

Lauri — Mon, 19 Oct 2020 15:28:06 GMT

Hi Wail, when I put your field names into my formula:

=Aggr(sum({<[Month]={">=$(=MonthStart(AddMonths(Today(), -$(vMonth1)))<=$(=MonthEnd(AddMonths(Today(), -$(vMonth1)))"}>} Size) - sum({<[Month]={">=$(=MonthStart(AddMonths(Today(), -$(vMonth2)))<=$(=MonthEnd(AddMonths(Today(), -$(vMonth2)))"}>} Size), [Name])

Note that your "Month" field must contain date values.

vMonth1 and vMonth2 are variables that your user fills in two variable boxes on screen.

Like I said, you may prefer to calculate 'yes/no' in the load script. It's a little simpler than Set Analysis, but not flexible for the user. (Though you could calculate 'yes/no' for 2 months, 3 months, 4 months, etc. in the load script.)

Do you need to calculate the 3-month change for every row?

Re: How filter on equipement whose data volume has not changed over 3 consecutives months?

Waïl_Riachi_Sanofi — Tue, 20 Oct 2020 13:06:27 GMT

Hi Lauri,

I want to know how we could filter on, for example, equipment whose data volume has not changed over 3 consecutive months, or even on equipment that has had an increase in data volume greater than 10 GB during the last quarter. For example, I would like to have only appeared the equipment for which the "Size" has not changed in January, February, March or June, July, August (3 months in a row). For the second case, for example to have only the equipment which had a "Size" greater than 10go the last semester.

Re: How filter on equipement whose data volume has not changed over 3 consecutives months?

Lauri — Tue, 03 Nov 2020 17:54:57 GMT

Wail, sorry I didn't reply sooner. Did you find a solution?

Re: How filter on equipement whose data volume has not changed over 3 consecutives months?

Waïl_Riachi_Sanofi — Wed, 09 Dec 2020 15:27:31 GMT

Lauri, no I didn't find a solution..