topic Re: Set Analysis Comparing 2 Dates in App Development

Set Analysis Comparing 2 Dates

dthornburg — Thu, 14 Mar 2024 12:08:41 GMT

Hi,

I am trying to total a group of orders that fall within a planned Cycle. I have the starting dates of each cycle and the start date for each order. Cycle is when we plan on running on the orders for a given family(First column in Planning detail). Trying something like below. How do I compare to the next cycle date. calculation below is not even working. Any help would be appreciated.

Sum({1<,PLN_StartDate={"=PLN_StartDate>=PLN_CycStartDate"}>}SUM(PLN_QtyOrdered))

Re: Set Analysis Comparing 2 Dates

JonnyPoole — Thu, 14 Mar 2024 13:01:47 GMT

Is it possible to calculate an 'End Date' for each cycle. If you have Start and End data for each cycle, you could perform an intervalmatch() which automatically forms a data model link from the order start date to the cycle whose start/end date range matches.

Once the link is in the data model, its very easy to calculate what you are after.

https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/IntervalMatch.htm#:~:text=The%20IntervalMatch%20prefix%20is%20used,one%20or%20several%20additional%20keys.&text=The%20IntervalMatch%20prefix%20must%20be,statement%20that%20loads%20the%20intervals.

Re: Set Analysis Comparing 2 Dates

MatheusC — Thu, 14 Mar 2024 13:03:38 GMT

Hi, @dthornburg

The function below did not work due to errors in the syntax of the written expression.

It was confusing to understand the model presented and what you want to achieve, but I followed the example of the expression presented.

See it this way

Sum({1<PLN_StartDate={">=PLN_CycStartDate<=PLN_StartDate"}>}PLN_QtyOrdered)

You may need to change something regarding the date period fields, as I said I don't understand very well, but I hope it helps to give you some guidance on the solution.

Regarts,
Matheus

Re: Set Analysis Comparing 2 Dates

dthornburg — Thu, 14 Mar 2024 17:02:03 GMT

Assume I have 2 records in the Cycle schedule table. Record 1 has a start date of 12/7/2023 and record 2 has a start date of 1/29/2024. I then have many records in another table that have start dates. The goal is to total all the records in 2nd table that are between the 2 start dates for each cycle record. In example I posted. if a pln_compl_date is between 1 start date and the next start date it is included in the 1st record.

Something like below but the last portion id looking at the next cycle schedule date.

Sum({1<,PLN_StartDate={"=PLN_StartDate>=PLN_CycStartDate"} AND PLN_StartDate={"=PLN_StartDate>=PLN_CycStartDate" }>}SUM(PLN_QtyOrdered))

Re: Set Analysis Comparing 2 Dates

JonnyPoole — Thu, 14 Mar 2024 17:09:57 GMT

I would use script logic to read record 1 and record 2 into a new (3rd) table in order to produce a singlerecord that has an start and end date where the start date is the start date from record 1 and the end date is the start date from record 2. Then... use intervalmatch() to form a link between the dates in the 2nd table and the interval in the 3rd table.

If you have sample data I can show you. It may be possible to achieve this through If logic and set analysis but it strikes me as burdensome and possibly slow to render