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

[arusiva89]

create two variables to capture the selected date ranges ..For ex : Variable A -> Min(date) and Varaible B->Max(Date) and use it in the set expression

[Kushal_Chawda]

if you want it dynamic based on date selection then try below

Replace today() with max(Date)

For Max Sales

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

For Max Date

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

[wuensche]

It does'nt work, if i select one date

I think the aggr() is not at the correct step.

Also i don't know what do you want 2x with max(date)?

[Vegar]

@wuensche 

It does'nt work, if i select one date

What is the result when you select one date and what is you expecting result? The suggested solution is getting the max sale per day in the selected period. So if you select one day you will get the sales of that day.  If you need another behaviour you will need to make changes to the set modifier in some way.

"I think the aggr() is not at the correct step."

In order to get the max sum(sale) per date you will need to use aggr unless you change approach and do the aggregation in your data modelling script. 

"Also i don't know what do you want 2x with max(date)?"

The first max(date) is used in the expression part and the second is used to set the rank sorting.

Did you look att my attached qvf file? Does this app provide  the same behavior as your application?

[wuensche]

@Vegar 

sorry, if I express myself not well.

I have to select one date, in generell yesterday, because the measures of the dashboard are values per day.

But for the measure pointer I got the condition to set the max border to the highest sales per day of the last 365 days.

So what I want is:

I select a date, for example yesterday 26th july  and the result should be for instance:

date:23th december 2019

sales of 23th december 2019: 200.000

SoI have to calculate the date and sum of sales of the calculated date, where we reach the maximum sales per day of the last 365 days.

I hope it is clear now?

[Vegar]

You might need to put a set in the  firstsortedvalue function as well. Try sometghing like this.

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