max sales of last 365 days

[wuensche]

Hello together,

i hope someone can help me.

I have to calulate for a measure pointer a maximum border.

The Max Value should show the max sale of between today and - 365 day (last year)

I think it should be something like:

Date is Datefield.

 sum({<Date= {">=$(=Date(today()-364,'DD.MM.YYYY'))<=$(=Date(today(),'DD.MM.YYYY'))"}>} Sales)

But where i have to put max function?

And how can i get the date of the max sum sale?

kind regards

[Vegar]

What do you mean by max sale?

Is it the maximum sales transaction amount? The maximum total sale per day? Per sales person?

A suggestion: maybe you could create a small data sample and explain what output you are expecting from that sample?

[wuensche]

I hope you understand the example:

I have a Table like this:

I have to sum the sales of all Markets per day.

Date	Market-ID	Sales
17.07.2020	815	5800
17.07.2020	563	1000
17.07.2020	587	5000
15.05.2020	815	2000
15.05.2020	563	3000
15.05.2020	587	1000
18.11.2019	815	6000
18.11.2019	563	4000
18.11.2019	587	12000
Max Sum Sales per Day
17.07.2020	11800
15.05.2020	6000
18.11.2019	22000

And the result shout be:

 one KPI 22000 and in the other KPI 18.11.2019

[Vegar]

Take a look at the image below. The grey boxes are expression used to get the blue boxes output.

[Kushal_Chawda]

try below if you want to show that in KPI object

For Max Sales

=max(aggr(sum({<Date={">=$(=today()-364)<=$(=today())"}>}Sales),Date))

For Max Date

FirstSortedValue(aggr(only({<Date={">=$(=today()-364)<=$(=today())"}>}Date),Date),
-aggr(sum({<Date={">=$(=today()-364)<=$(=today())"}>}Sales),Date))

[Vegar]

Thank you  @Kushal_Chawda . I totally forgot to put the last 365 days set criteria into my expressions.

[Kushal_Chawda]

@Vegar  No problem. Your expression just works fine. I have just modified for 365 days set.

[wuensche]

Thanks for your help. But it does not work for me. I don't have the table, where the sales are summed up.

Max Sum Sales per Day
17.07.2020	11800
15.05.2020	6000
18.11.2019	22000

I have to do all steps in one function.

[Vegar]

The examples provided by me and @Kushal_Chawda  does not need an pre aggregated data set. See attached qvf which are using the data set you provided before.

LOAD * inline [
Date, Market-ID,Sales
17.07.2020,815,5800
17.07.2020,563,1000
17.07.2020,587,5000
15.05.2020,815,2000
15.05.2020,563,3000
15.05.2020,587,1000
18.11.2019,815,6000
18.11.2019,563,4000
18.11.2019,587,12000];

[wuensche]

thx, I understand.

But my problem is, that I have to take some more value for the function and I can't post it.

In my function I get allways the sales of the selected Date.

I dont take the function today(), because people can select also other dates. I do it with a variable