topic Re: Max Possible Date based on Working Day selection, with Alternate States in QlikView

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

retko1985 — Mon, 16 Oct 2017 14:26:49 GMT

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.

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

sunny_talwar — Mon, 16 Oct 2017 14:33:08 GMT

Try this

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

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

retko1985 — Tue, 17 Oct 2017 11:13:13 GMT

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.

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

sunny_talwar — Tue, 17 Oct 2017 11:30:11 GMT

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

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

retko1985 — Tue, 17 Oct 2017 11:52:11 GMT

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.

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

sunny_talwar — Tue, 17 Oct 2017 11:57:13 GMT

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)

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

sunny_talwar — Tue, 17 Oct 2017 11:58:03 GMT

Or this

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

retko1985 — Tue, 17 Oct 2017 12:07:24 GMT

This works like a charm

Very much appreciate.

Thank you.