4 Replies Latest reply: Sep 21, 2018 5:46 AM by Mahitha M

# Set analysis

Hi Experts,

In the below attached app ,In Month end Trend chart need to get 75,836 value instead of Zero. I have included the formula in the application in this chart.

The values are coming like In Jul Month Max Reporting date is 19/07/2018 that date value 75,836 need to show on trend same for aug.

For aug getting correct value as max(date) value.

• ###### Re: Set analysis

Hi.Try this expression

=Sum(Aggr((Sum({<Type={'A'},Date={\$(=Chr(39)&Concat(DISTINCT Date(MonthEnd(Date),'DD/MM/YYYY'), Chr(39)&Chr(44)& Chr(39))& Chr(39))}>}Amount)* Avg({<Date={\$(=Chr(39)&Concat(DISTINCT Date(MonthEnd(Date),'DD/MM/YYYY'), Chr(39)&Chr(44)& Chr(39))& Chr(39))}>}A1%)),Date,Dept,Branch))

or the same but a little bit shorter

=Sum(Aggr((Sum({<Type={'A'},Date={\$(='"'&Concat(DISTINCT Date(MonthEnd(Date),'DD/MM/YYYY'), '","')&'"')}>}Amount)* Avg({<Date={\$(='"'&Concat(DISTINCT Date(MonthEnd(Date),'DD/MM/YYYY'), '","')&'"')}>}A1%)),Date,Dept,Branch))

• ###### Re: Set analysis

I'm sorry, I have no QS in front of me.

• ###### Re: Set analysis

Hi Mahita,

I can suggest you to create a flag in the load script, that indicate the last day of month, the max([Report Date]) returns the max date of the whole application, not by dimension.

G.

• ###### Re: Set analysis

Hi,

I have found the solution.

Below Expression is working fine.

=Sum(Aggr((Sum({<Type={'A'},Date=p(New_Date)>}Amount)*

Avg({<Date=p(New_Date)>}A1%)),Date,Dept,Branch))