topic Re: Time dimension/max(date) accumulates value in QlikView

Time dimension/max(date) accumulates value

afbraga1 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Time dimension/max(date) accumulates value

sunny_talwar — Thu, 03 Aug 2017 11:46:42 GMT

Try this

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

Re: Time dimension/max(date) accumulates value

Anil_Babu_Samineni — Thu, 03 Aug 2017 11:48:20 GMT

May be do this?

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

Re: Time dimension/max(date) accumulates value

effinty2112 — Thu, 03 Aug 2017 12:16:46 GMT

Hi Andre,

Maybe

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

Regards

Andrew

Re: Time dimension/max(date) accumulates value

afbraga1 — Thu, 03 Aug 2017 12:58:36 GMT

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)

Re: Time dimension/max(date) accumulates value

sunny_talwar — Thu, 03 Aug 2017 13:04:51 GMT

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))