QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Not applicable

How to perform set analysis between two dynamically changing dates

Hello All,

Below is what I am trying to do.

sum({<[Part Date] = {">=\$(=\$(vStartDate))<=\$(=\$(vEndDate))"}>} Quantity)

[Part Date]- It is the date when a Product is manufactured.

vStartDate- It is a variable which dynamically changes (today()-number of days a product takes to fail)

So basically I am trying to summarize the Quantity between the two dates which changes dynamically.

Regards

Dharshan

1 Solution

Accepted Solutions
MVP

With dynamically, do you mean it needs to consider the current dimensional value? This won't work.

If not, just ensure that your variables are returning a correct date format when expanded, then it should be enough to use one dollar sign expansion per variable:

sum({<[Part Date] = {">=\$(vStartDate)<=\$(vEndDate)"}>} Quantity)

Dates in Set Analysis

9 Replies
MVP

With dynamically, do you mean it needs to consider the current dimensional value? This won't work.

If not, just ensure that your variables are returning a correct date format when expanded, then it should be enough to use one dollar sign expansion per variable:

sum({<[Part Date] = {">=\$(vStartDate)<=\$(vEndDate)"}>} Quantity)

Dates in Set Analysis

Contributor III

sum({<[Part Date] = {">=\$(=vStartDate)<=\$(=vEndDate)"}>} Quantity)

If vStartDate and vEndDate are formulas, you need to use the equal sign in front of variables.

Ex: vStartDate: Today()

Make sure Part Date, vStartDate and vEndDate are all dates or all numbers.

Try something like this

sum({<[Part Date] = {">=" & '\$(vStartDate)' "<=" '\$(vEndDate)'}>} Quantity)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

Hello Stefan,

By dynamic I meant it the dates change dynamically so should the value. But even with no dimension and when I assign some static dates for both the vStartDate and  vEndDate variables your formula is not giving expected results.

the dates are in the format MM/DD/YYYY I mean all the three dates are in the same format.

Not applicable
Author

Hey Anil,

Not applicable
Author

Will it be a problem if my [Part Date] column had NULL values in it?

Try Like this

vStartDate = Date(Date#(Field, 'MM/DD/YYYY'),'MM/DD/YYYY')

sum({<[Part Date] = {">=\$(vStartDate)<=\$(vEndDate)"}>} Quantity)

I really don't know where do you struck. Would you provide application.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

Hello All,

I got the solution. The issue was with my Part Date which was displaying in 'MM/DD/YYYY' for mat but was not recognized by Qlikview as a date. Thanks a lot for your support

Contributor II

You said

"With dynamically, do you mean it needs to consider the current dimensional value? This won't work."

Is there any workaround? I'm taking period from table and need to calculate sum between  startdate and startdate+period.

I've already break my brain to figure out how to solve that limitation...

Community Browser