topic Re: Compare two date dimensions using Set Analysis Expression in QlikView

Compare two date dimensions using Set Analysis Expression

Tanya_24 — Thu, 27 Apr 2023 06:55:33 GMT

Hi All

I have all the columns as date columns instead of Invoice Amount column. I am trying to do the sum of Invoice Amount based on the below condition, but it is not working for me.

1st condition : I want to check Product Start Date and Sales Date is not null.

2nd condition : I want to check Approval End Date less than Product Expiry Date.

Set Analysis Expression :-

Sum({$< [ Product Start Date ]={"*"}, [Sales Date]={"*"},[Approval End Date] = {"<=$(Date('[Product Expiry Date]', 'DD/MM/YYYY'))"}>}[Invoice Amount])

Sum I am getting is : 158,050.58 but Expected result is : 156,537.08

All Date formats are in (DD/MM/YYYY)

Would Appreciate for the help!:)

Re: Compare two date dimensions using Set Analysis Expression

a_mullick — Thu, 27 Apr 2023 09:04:01 GMT

Hi,

Just to check your second condition:

2nd condition : I want to check Approval End Date less than Product Expiry Date.

But the logic in your set analysis is less than or equal to:

[Approval End Date] = {"<=$(Date('[Product Expiry Date]', 'DD/MM/YYYY'))"}>}[Invoice Amount])

Re: Compare two date dimensions using Set Analysis Expression

Tanya_24 — Thu, 27 Apr 2023 11:52:42 GMT

Hi

It is less than equal to only . I forgot to write equal to in 2nd condition

Re: Compare two date dimensions using Set Analysis Expression

BrunPierre — Thu, 27 Apr 2023 12:41:47 GMT

In this case, creating a flag in the script would be an even better way.

If([Approval End Date] <= [Product Expiry Date], 1, 0) as Flag

and then this

Sum({$< [ Product Start Date ]={"*"}, [Sales Date]={"*"},Flag = {1}>}[Invoice Amount])

Re: Compare two date dimensions using Set Analysis Expression

tresB — Thu, 27 Apr 2023 12:43:26 GMT

Right syntax for such field comparison would be like:

[Approval End Date] = {"=[Approval End Date]<=[Product Expiry Date]"}

Re: Compare two date dimensions using Set Analysis Expression

Tanya_24 — Thu, 27 Apr 2023 15:02:06 GMT

Approval End Date and Product Expiry Date are in different tables and there is no direct link between tables.

Re: Compare two date dimensions using Set Analysis Expression

Tanya_24 — Thu, 27 Apr 2023 23:31:24 GMT

Hi,

Above expression is giving me the less value than the expected result. It is not working.

Re: Compare two date dimensions using Set Analysis Expression

tresB — Fri, 28 Apr 2023 04:58:33 GMT

'giving less value'... might be becasue of some other issues but the set analysis syntax. Working of set analysis and IF structure is not same. It could be the case that you need IF, like below :

Sum({$< [ Product Start Date ]={"*"}, [Sales Date]={"*"}>} If( [Approval End Date] <=[Product Expiry Date], [Invoice Amount]))

Try the above expression.

Re: Compare two date dimensions using Set Analysis Expression

Prem0212 — Fri, 28 Apr 2023 06:22:36 GMT

I think this expression works for you.

Sum({$< [ Product Start Date ]<>{"Null"}, [Sales Date]<>{"Null"},[Approval End Date] = {"<=$(Date('[Product Expiry Date]', 'DD/MM/YYYY'))"}>}[Invoice Amount]).

Please like and accept the solution if u liked it.

Re: Compare two date dimensions using Set Analysis Expression

Tanya_24 — Mon, 01 May 2023 00:40:34 GMT

It is giving Sum as 0, not working. Thank you 🙂

Re: Compare two date dimensions using Set Analysis Expression

Tanya_24 — Mon, 01 May 2023 00:44:35 GMT

This expression is not working as it is giving sum of 5 lakh something.
Just wanted to know will set analysis work if columns are present in two different tables and there is no easy link between them?

Re: Compare two date dimensions using Set Analysis Expression

tresB — Tue, 02 May 2023 04:25:28 GMT

Mostly not. In such cases, if structure is a better bet. However, you have to still validate and understand the association correctly, i.e. - the granularity, ...