Set Analysis Not Working With Today()

[Kelliesy]

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?

[p_verkooijen]

@Kelliesy can you share a preview of the Quarter content? 

Table with dimension Quarter and metrics with TEXT(Quarter) and NUM(Quarter)

[Kushal_Chawda]

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

[marcus_sommer]

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.

[Kelliesy]

Here you go 

[Kelliesy]

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.  

[marksouzacosta]

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.

[Kelliesy · Accepted solution]

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.  

[marksouzacosta]

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)