Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis - Date From and To Variables

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

1 Solution

Accepted Solutions
sunny_talwar

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:

Why don’t my dates work?

Get the Dates Right

Dates in Set Analysis

View solution in original post

2 Replies
sunny_talwar

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:

Why don’t my dates work?

Get the Dates Right

Dates in Set Analysis

Not applicable
Author

Thank you for your advise.

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

/Brad