topic Re: How to get values between two dates using Set Analysis in QlikView

How to get values between two dates using Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

In my report I have a STOCKDATE field and a STOCKVALUE field - I want to get the value (STOCKVALUE) based on the date in the last 12 months from todays date, stock over 1year old, stock over 2 years old etc. How can I do this?

Re: How to get values between two dates using Set Analysis

isingh30 — Tue, 27 Feb 2018 16:31:13 GMT

Try this -

only({<STOCKDATE = {'date,---'}>}STOCKVALUE)

Thank you!

Re: How to get values between two dates using Set Analysis

Chanty4u — Tue, 27 Feb 2018 16:31:14 GMT

try this

create two variables to read the start and end date,

then try like this:

count({<Calender_Date={">=$(=Date(vStartDate,'MM/DD/YYYY'))<=$(=Date(vEndDate,'MM/DD/YYYY'))"}>}Dimension)

Note: All date format should be same.

Re: How to get values between two dates using Set Analysis

Digvijay_Singh — Tue, 27 Feb 2018 16:34:15 GMT

Something like this -

Sum({<STOCKDATE = {"<=$(=today())>=$(=addmonths(today(),-12))"}>}STOCKVALUE)

Re: How to get values between two dates using Set Analysis

Anonymous — Tue, 27 Feb 2018 16:57:34 GMT

I have created two variables vStartDate (=Min(STOCKDATE) and vEndDate =Max(STOCKDATE) and then the formula i used was:

SUM({<STOCKDATE={">=$(=Date(vStartDate,'MM/DD/YYYY'))<=$(=Date(vEndDate,'MM/DD/YYYY'))"}>}STOCKVALUE)

I get 0... not sure why? what have i done wrong?

Re: How to get values between two dates using Set Analysis

Anonymous — Tue, 27 Feb 2018 16:58:57 GMT

my STOCKDATE field is a datetime field if that makes a difference?

Re: How to get values between two dates using Set Analysis

Anonymous — Tue, 27 Feb 2018 17:00:25 GMT

Using Sum({<STOCKDATE1={"<=$(=today())>=$(=addmonths(today(),-12))"}>}STOCKVALUE) I get zero as well

Re: How to get values between two dates using Set Analysis

Anonymous — Tue, 27 Feb 2018 17:10:31 GMT

I amended my data import to convert the datetime field into just a date field and it works! Thankyou very much for your help!!