topic Re: Set Analysis - Date From and To Variables in QlikView

Set Analysis - Date From and To Variables

Sun, 05 Feb 2017 00:42:02 GMT

Hi i am stuck with the following problem that my Set analysis returns 0.

my Dates in the Qlikview application are strings i believe.

so i have created a few variables to help me along the way in doing a R13_24 Analysis.

Here is the query Realised with Variables

=Sum({$<Year_Month=,Bokningsdatum= {">=$(=vStartDate) <=$(=vEndDate)"}>} Belopp_HB)

vMaxBokningsDatum)=max(Bokningsdatum)

vMinBokningsDatum=min(Bokningsdatum)

vStartDate = addmonths(monthend($(vMinBokningsDatum))-1,-12)

vEndDate = addmonths(monthend($(vMaxBokningsDatum))-1,-12)

Here is what it looks like with the variables exposed

Sum({$<Bokningsdatum= {">=addmonths(monthend(min(Bokningsdatum))+1,-24) <=addmonths(monthend(max(Bokningsdatum))-1,-12)"}>} Amount)

Can anyone point me in the right direction. is my set analysis wrong or are my fields not compatible?

Best

Brad

sunny_talwar — Sun, 05 Feb 2017 03:28:11 GMT

What is your date field format here? Do you use Date() function in the script for Bokningsdatum? Like this

Date(Bokningsdatum) as Bokningsdatum

or do you provide your own format?

Date(Bokningsdatum, 'DD/MM/YYYY') as Bokningsdatum

If you are doing it the second way, you might need to use Date() function in your expressions

=Sum({$<Year_Month=, Bokningsdatum= {">=$(=Date(vStartDate, 'DD/MM/YYYY')) <=$(=Date(vEndDate, 'DD/MM/YYYY'))"}>} Belopp_HB)

Read about dates in general and dates in set analysis here:

Sun, 05 Feb 2017 09:50:49 GMT

Thank you for your advise.

i will reformat the date field in the script and try again.

/Brad