topic Re: Showing minimum data among last 3 years in App Development

Showing minimum data among last 3 years

Aspiring_Developer — Thu, 27 Aug 2020 20:15:56 GMT

Hello,

Please help on this one :-

Data

Year, Data, AVG(Data)

2017 50 1

2018 20. 2

2019 30. 3

2020 5. 4

My requirement is to calculate the min of AVG(Data)

So that I should get min(AVG (Data) = 1 as 1 is the minimum value in the above table

And we have to apply this only for latest 3 years . As of now I have the data for till 2020

I have to show it in bar graph with dimension as Year . How can I achieve this ?

@sunny_talwar @Kushal_Chawda

Re: Showing minimum data among last 3 years

Kushal_Chawda — Thu, 27 Aug 2020 21:14:39 GMT

try below expression

=min({<Year={">=$(=max(Year)-3)"}>}Avg_Data)

Re: Showing minimum data among last 3 years

Aspiring_Developer — Thu, 27 Aug 2020 21:22:26 GMT

@Kushal_Chawda hello kush ,firstly many thanks for your instant response and I am struggling due to tight deadline.

As per your solution,

In my data I am getting only 2 feilds from backend Year & Data

AVG (Data) I have valuated in front measure

I am not sure if I can mass this front end measure in the expression. Please confirm

Re: Showing minimum data among last 3 years

Aspiring_Developer — Thu, 27 Aug 2020 21:32:55 GMT

@Aspiring_Developer wrote:
@Kushal_Chawda hello kush ,firstly many thanks for your instant response and I am struggling due to tight deadline.
As per your solution,
In my data I am getting only 2 feilds from backend Year & Data
AVG (Data) I have valuated in front measure
I am not sure if I can mass this front end measure in the expression. Please confirm

@Kushal_Chawda

Desired output

Year Data

2017. 50

2018 20

2019 30

2020 5

Desired output

To get measure column

Min(AVG(Data))

Can we use the calculated measure column AVG(Data) to get the other column measure Main(AVG ( Data)) returning minimum values

Re: Showing minimum data among last 3 years

Kushal_Chawda — Thu, 27 Aug 2020 21:36:25 GMT

What do you mean by this (Min(Avg(Data))?

Re: Showing minimum data among last 3 years

Kushal_Chawda — Thu, 27 Aug 2020 21:47:24 GMT

may be you are looking for this

=sum({<Year={">=$(=max(Year)-3)"}>}Data)

Re: Showing minimum data among last 3 years

Aspiring_Developer — Thu, 27 Aug 2020 21:55:16 GMT

@Kushal_Chawda

Avg ( Data ) will give us the average values for each year. I want to show the minimum values for these AVG(Data) in a chart like below

Year AVG( data) minimum of AVG(Data)

2017 20. 20

2018 30 -

So the column should 20 as it is the small average values in the years

Re: Showing minimum data among last 3 years

Kushal_Chawda — Thu, 27 Aug 2020 22:08:32 GMT

try below with Year as dimension and below expression

if(avg(Data)= min(total aggr(avg(Data),Year)),Avg(Data),0)

Go to chart properties->add-on-> uncheck include zero values

Re: Showing minimum data among last 3 years

Aspiring_Developer — Fri, 28 Aug 2020 07:36:10 GMT

@ Kush

if(avg(Data)= min(total aggr(avg(Data),Year)),Avg(Data),0)

I want to show it for last 3 years . If i will take Year, it will give me the data for all Years.

@sunny_talwar

Re: Showing minimum data among last 3 years

Aspiring_Developer — Fri, 28 Aug 2020 12:19:32 GMT

@Kushal_Chawda

Used the expression, didn't worked

See attached screenshot please

Re: Showing minimum data among last 3 years

sunny_talwar — Fri, 28 Aug 2020 12:49:06 GMT

For last 3 years, may be add @Kushal_Chawda's set analysis to his if expression

If(Avg({<Year = {">=$(=Max(Year)-3)"}>} Data) = Min({<Year = {">=$(=Max(Year)-3)"}>} TOTAL Aggr(Avg({<Year={">=$(=Max(Year)-3)"}>} Data), Year)), Avg({<Year={">=$(=Max(Year)-3)"}>} Data), 0)

Re: Showing minimum data among last 3 years

Aspiring_Developer — Fri, 28 Aug 2020 13:35:41 GMT

@sunny_talwar

Tried the above, giving the data as 0 in entire column :'(

Re: Showing minimum data among last 3 years

Aspiring_Developer — Fri, 28 Aug 2020 13:38:06 GMT

@sunny_talwar

Re: Showing minimum data among last 3 years

sunny_talwar — Fri, 28 Aug 2020 13:41:24 GMT

You didn't mention about Project Code dimension... may be try this

If(Avg({<Year = {">=$(=Max(Year)-3)"}>} Data) = Min({<Year = {">=$(=Max(Year)-3)"}>} TOTAL <[Project Code]> Aggr(Avg({<Year={">=$(=Max(Year)-3)"}>} Data), Year)), Avg({<Year={">=$(=Max(Year)-3)"}>} Data), 0)

The response is dependent upon the information we have.

Re: Showing minimum data among last 3 years

Aspiring_Developer — Fri, 28 Aug 2020 13:59:05 GMT

@sunny_talwar

sorry,

it is coming zero without project_code :

Re: Showing minimum data among last 3 years

Kushal_Chawda — Fri, 28 Aug 2020 15:00:51 GMT

I think you confused us with min(Avg(Data)). Looks like you already have average calculated so you need minimum of that for particular year.

Try below with dimension Year and ProjectCode and below expression

=if(sum([avg hourly Rate])=sum(total <Year>aggr(min([avg hourly Rate]),Year)),sum({<Year ={">=$(=max(Year)-3)"}>}[avg hourly Rate]),0)

Re: Showing minimum data among last 3 years

Kushal_Chawda — Fri, 28 Aug 2020 15:02:56 GMT

Make sure to suppress zero values