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: 
Ja123__
Partner - Creator
Partner - Creator

Set analysis, less than or equal to, variable. Not calculating correctly.

Hi there

I have been given a QlikView model where there was a date input (vLoanBalanceDate) and this date was used in the following first measure below.

I am now on QlikSense recreating their model and want to calculate the same measure, using variables I have created, instead of the vLoanBalanceDate input.

I now have variables that contain the maximum dates I need to use in my set analysis. Could you please assist me with the set analysis script. I will attach a photo of the dates reflecting correctly in the new variables and also the statement I thought was right?

 

The original measure on the old QlikView model I was given:

sum(if ([Transaction Date]<= vLoanBalanceDate, if (Effect = 'Debit', [Transaction Amount], -[Transaction Amount]))

My new measure not working along with the new variables I need to use: (One to calculate the cumulative sum up to the max date on the selection and the other I want to show what the sum of tx's was a year ago)

Capture.PNG

 

The new script I have tried:

sum({$< [Transaction Date] = {'<=$(=vMaxTDate)'} > } if(Effect = 'Debit', [Transaction Amount], -[Transaction Amount]))

I am butting my head against the wall with this one. I have tried removing the = before vMaxTDate as well...

 

 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try with double quotes

Sum({$<[Transaction Date] = {"<=$(=vMaxTDate)"}>} If(Effect = 'Debit', [Transaction Amount], -[Transaction Amount]))

Also, try this

Sum({$<[Transaction Date] = {"<=$(=$(vMaxTDate))"}>} If(Effect = 'Debit', [Transaction Amount], -[Transaction Amount]))

View solution in original post

3 Replies
sunny_talwar

Try with double quotes

Sum({$<[Transaction Date] = {"<=$(=vMaxTDate)"}>} If(Effect = 'Debit', [Transaction Amount], -[Transaction Amount]))

Also, try this

Sum({$<[Transaction Date] = {"<=$(=$(vMaxTDate))"}>} If(Effect = 'Debit', [Transaction Amount], -[Transaction Amount]))
dplr-rn
Partner - Master III
Partner - Master III

Try Using double quotes in your set. when using 'search' things in set analysis you should use ""
{$< [Transaction Date] = {"<=$(=vMaxTDate)"} > }

vanderson1
Contributor
Contributor

I could really use some help with a variable calculation.  I have tried multiple options with no resolution.  The variable will only populate when the selected date matches.  But my table pulls records when the date is less than or equal selected. I have tried the following expressions.  All are accepted, but result is 0 unless selected date matches. Any help would be greatly appreciated.

=${1<>}$(vAnticipatedRevenue)

=${< [App.AppReceivedDate] = {"<=$(=Max([App.AppReceivedDate]))"}>}$(vAnticipatedRevenue)

=IF(NOT ISNULL(max({$<[App.AppReceivedDate]={"<=$(=max([App.AppReceivedDate]))"}>}[App.AppReceivedDate])), $(vAnticipatedRevenue))