
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Set Analysis Not Working With Today()
I'm struggling to figure out why my measure expression is not working the way I think it should. I have a qvd with a field in it named "Quarter". In the load script for my QVD I have the "Quarter" field formatted like this;
Date(Quarter,'M/D/YYYY') as Quarter.
The table also has "ID" and "QuarterTotal" fields. In my app I load the three fields without any other modifications. In my sheet, in the app, I create a table and try to make a field that will sum the "QuarterTotal" field when the Quarter is less than or equal to today. However, it's not working at all. I have tried the follow;
Sum({<Quarter= {"< $(=Today())"},>} QuarterTotal)
Sum({< Quarter= {"<= $(=Date(Today(),'M/D/YYYY'))"} >} QuarterTotal)
What really confuses me is that if I switch to Sum(If(Quarter< Today(), QuarterTotal)) it works just fine.
I have validated that Quarter is being read as a date. Any ideas?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks all for the help. I actually played around and figured out how to fix it. I updated the format to Date(Quarter,'M/DD/YYYY') and it worked. No idea why but I guess it's just super particular about date formatting.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Kelliesy can you share a preview of the Quarter content?
Table with dimension Quarter and metrics with TEXT(Quarter) and NUM(Quarter)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Kelliesy I see typo in your expression (additional comma). I don't know whether it's typo here or in your actual expression as well. try removing that :Sum({<Quarter= {"< $(=Today())"},>} QuarterTotal)
if it's not typo try below expression if Quarter is formatted as date
Sum({<Quarter= {"< $(=floor(Today()))"}>} QuarterTotal)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The if-loop evaluates the conditions on a row-level which is the opposite to the set analysis which evaluates against the column-level and worked in general like a selection. Therefore it depends on the data-set and wanted view which approach is in general possible respectively more suitable.
I assume that if your if-loop returned the right results that your scenario couldn't be solved with the set analysis.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here you go

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oops, the typo was me in this chat not in Qlik. I did try floor but I'm still getting 0 instead of the amount should be getting.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your expression looks ok. Can you please share a screen shot with a table chart with just dimensions: Quarter and QuarterTotal? Also, show us the relationship between Quarter and QuarterTotal tables and the density and subset ratio of each key field and the Quarter field tags too please.
Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks all for the help. I actually played around and figured out how to fix it. I updated the format to Date(Quarter,'M/DD/YYYY') and it worked. No idea why but I guess it's just super particular about date formatting.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Usually, I create a pure numeric version of Date fields and use it on my Set Analysis. It makes things so much easier to handle. There is an excellent that has a special chapter only about that: https://a.co/d/1Im8mt8 (QlikView 11 Developer's: Develop Business Intelligence Applications With Qlikview 11)
Read more at Data Voyagers - datavoyagers.net
Follow me on my LinkedIn | Know IPC Global at ipc-global.com
