Time dimension/max(date) accumulates value

[afbraga1]

Hello,

Lately I've be having an issue and I can't seem to find the way to solve it. Conceptually, I want to get the invoiced quantity that was not confirmed in the selected month.

I have created an example that displays the problem I'm facing.

For August I have this as the invoiced quantity that was not confirmed in August.

For Jul I have this as the invoiced quantity that was not confirmed in Jul.

The issue is when I select 2 months at the same time. I want to see in July he 370 but I'm getting the 1070 because it is considering the max(BillDate) of the two months.

I've attached a QVW below that shows this.

Thank you for your time, hopefully you can help me!

Best regards,

André Braga

[sunny_talwar]

Try this

Sum(Aggr(If(ConfirmDate <= MonthEnd(AddMonths(Max(Total <House, BillMonth> BillDate),-1)), Quantity), House, BillMonth, ConfirmDate))

[Anil_Babu_Samineni]

May be do this?

If(GetFieldSeletions(Month)>0, First 2 images expressions, normal expression)

[effinty2112]

Hi Andre,

Maybe

Sum(if(Month(BillDate) <> Month(ConfirmDate), Quantity))

Regards

Andrew

[afbraga1]

What if I had more parameters in the set analysis how would I put it in your formula. In this case a Doc.Source='Invoice'?

In my previous formula I would do it like this:

sum({<Doc.Source='Invoice',ConfirmDate={"<=$(=(MonthEnd(AddMonths(Max(BillDate),-1))))"}>}Quantity)

[sunny_talwar]

Those can stay as set analysis....

Sum({<Doc.Source = {'Invoice'}>} Aggr(If(ConfirmDate <= MonthEnd(AddMonths(Max(Total <House, BillMonth> BillDate),-1)), Quantity), House, BillMonth, ConfirmDate))