topic Set Analysis in QlikView

Set Analysis

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

I need the Sum([Open Balance]) where the [SheetName] is the greatest (or latest date) the date is set up like MM/DD/YY.

I think the MM/DD/YY might be screwing up the set analysis. It maybe a text string and that could be an issue.

Thanks,

Jimmy

Anonymous — Mon, 11 Jul 2016 14:15:22 GMT

if your date is defined as text you need to convert it to a dateformat using the date# function

date#(yourtextdatefield,'MM/DD/YY')

jeckstein — Mon, 11 Jul 2016 14:18:55 GMT

Rudolf,

It is currently using the #Date.

date(date#('$(vExcelSheets)','MM.DD.YY'),'MM/DD/YYYY')

sunny_talwar — Mon, 11 Jul 2016 14:24:54 GMT

Look for fixing your date in the script. May be these links come in handy:

Anonymous — Mon, 11 Jul 2016 14:45:30 GMT

how is your Expression with set Analysis Looks like?

jeckstein — Mon, 11 Jul 2016 14:54:39 GMT

I just figured it out. This was the expression I was looking for:

=Money(Sum({1<SheetName={"$(MaxSheetName)"}>}[Open Balance]))

$(MaxSheetName) was a variable I had to create that was defined as =Max(SheetName)