topic Re: How to perform set analysis between two dynamically changing dates in QlikView

How to perform set analysis between two dynamically changing dates

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to perform set analysis between two dynamically changing dates

swuehl — Tue, 13 Sep 2016 16:57:22 GMT

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

Re: How to perform set analysis between two dynamically changing dates

Anonymous — Tue, 13 Sep 2016 17:09:08 GMT

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.

Re: How to perform set analysis between two dynamically changing dates

Anil_Babu_Samineni — Tue, 13 Sep 2016 17:15:40 GMT

Try something like this

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

vEndDate- addmonths(vStartDate),6

Re: How to perform set analysis between two dynamically changing dates

Wed, 14 Sep 2016 03:43:01 GMT

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.

Re: How to perform set analysis between two dynamically changing dates

Wed, 14 Sep 2016 03:44:58 GMT

Hey Anil,

Your formula has errors.

Re: How to perform set analysis between two dynamically changing dates

Wed, 14 Sep 2016 13:14:34 GMT

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

Re: How to perform set analysis between two dynamically changing dates

Anil_Babu_Samineni — Wed, 14 Sep 2016 15:32:40 GMT

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.

Re: How to perform set analysis between two dynamically changing dates

Wed, 14 Sep 2016 20:29:28 GMT

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

Re: How to perform set analysis between two dynamically changing dates

Yury — Thu, 04 Apr 2019 10:21:16 GMT

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