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

[Ja123__]

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)

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

[sunny_talwar · Accepted solution]

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]

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

[vanderson1]

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