How to perform set analysis between two dynamically changing dates

[#1]

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)

vEndDate- addmonths(vStartDate+6)

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

Regards

Dharshan

[swuehl · Accepted solution]

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

[Anonymous]

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.

[Anil_Babu_Samineni]

Try something like this

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

vEndDate- addmonths(vStartDate),6

[#5]

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.

[#6]

Hey Anil,

Your formula has errors.

[#7]

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

[Anil_Babu_Samineni]

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.

[#9]

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

[Yury]

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...