Max Possible Date based on Working Day selection, with Alternate States

[retko1985]

Hello,

please refer to the attached QVW.

Basically I want in Set Analysis return 1/2/2017 (max white value) when I select Working Day 1.

I am now ignoring Working Days in set analysis, because in the end I want to use it for YTD computation. So If I select February and Working day 1 - it will count all sales for january + 1 day in February.

In model example, when I click Working Day 1, I would expect to see 300 in Sales (100 + 200). The problem is that my code is return last atualdate from all dates,not the white possible.

Please note that straight table is in Alternate state, so I need syntax for it. (other listboxes are in inherited state).

Thank you very much for help.

[sunny_talwar]

Try this

Sum({<Working_Day=, Actual_Date={"<=$(=Date(max({<Working_Day = P({$}Working_Day)>}Actual_Date)))"}>}Sales)

[retko1985]

Hi, thank you. Somehow it doesn't work in my script

This is what I use as an expression:

Sum({<Segment = {'A', 'L', 'S'}, Year=$::Year, Month=, Quarter=, Working_Day_Of_Month=, actualdate={"<=$(=Date#(max({<Working_Day_Of_Month = P({$}Working_Day_Of_Month)>}actualdate)))"}>}Invoice_Amount)

This is what I get:

But I want to have 01-02-2017 when I select Feb and Working day 1.

I had to change date to date# to recognize the format.

Also I noticed, that if I change the straight table to inherited state it gives the right date 01-02-2017.

So is it possible that syntax is not for alternate state?

Can you help me here more please?

Thank you.

[sunny_talwar]

Working_Day_Of_Month filter is in the inherited state or default state?? Would you be able to share a sample?

[retko1985]

Working_Day_Of_Month is in inherited state. This picture is from original application, which I cant share, but it is weird, since in the one I shared before it works fine.

[sunny_talwar · Accepted solution]

May be try this

Sum({<Segment = {'A', 'L', 'S'}, Year=$::Year, Month=, Quarter=, Working_Day_Of_Month=, actualdate={"<=$(=Date#(max({<actualdate= P({$}actualdate)>}actualdate)))"}>}Invoice_Amount)

[sunny_talwar]

Or this

Sum({<Segment = {'A', 'L', 'S'}, Year=$::Year, Month=, Quarter=, Working_Day_Of_Month=, actualdate={"<=$(=Date#(max({<Working_Day_Of_Month = P({$}Working_Day_Of_Month), Month = P({$} Month)>}actualdate)))"}>}Invoice_Amount)

[retko1985]

This works like a charm

Very much appreciate.

Thank you.