9 Replies Latest reply: Oct 25, 2010 11:45 AM by Shumail Hussain RSS

    SET Analysis - date filter

    Consulting nn

      I have a formula like this (where MonthOb is a variable out of a slider)





      Following my "learning curve" and lots of advice on topic, I thought to replace it with






      It doesn't work because the $ replacement puts a NUMBER within brackets as a selection. Though the number content works with the if clause apparently it doesn't as a set parameter. If I replace the field with the literal "Feb" instead of "2" it does work.


      I also am trying some formulas to obtain the month without success but don't see why it should get so complicated if the field content is a number. Where is the mistake?


        • SET Analysis - date filter
          Neil Miller

          Could you post a sample? This one may be a little easier if we could see what format your data is in.

          From your description, it seems like you have stored the month as a string (e.g. 'Jan'). Is this the case? Do you have it anywhere where the month is stored as a number? I find it much easier to use numerical months as then you're not left wondering whether to use Jan, January, etc. If you don't have a numeric month (or a normal date) within your data, it may be worthwhile to add it in. Here's a recent discussion on going from month number to name, but the same ideas would apply: http://community.qlik.com/forums/t/17054.aspx

          You could try using something like: ({$<month_chius={$(#=Date(MakeDate(1900, MonthOb), 'MMM'))}>}qta_ven)


            • SET Analysis - date filter
              Consulting nn

              I tried other times to post files without success. I would have anyway to make a simpler one as a sample.

              Already looked at the post you linked. The *** thing is that the variable contains a number. I displayed it with a text box to make sure.

              Obviously there is the usual SET in the script to provide IT month names:





















              MonthNames='Gen;Feb;Mar;Apr;Mag;Giu;Lug;Ago;Set;Ott;Nov;Dic'; DateFormat='DD/MM/YYYY';

              I tried with your formula adapted (replace text mese=month) if you place the expression in a text box the MMM (Feb) comes out. The same expr within the {} results in a 153 (days after 1900 - if you change the year the value increases) value that I detect using a blank label where the value is evaluated prior to using it in the table. Allow me to say that this would be a workaround. There is no reason why the if clause accepts a value and the SET should need the linked description. It is like in within {} the FORMAT is sterylized.

              =Date(MakeDate(1900, MeseOb,1), 'MMM')










              ({$<mese_chius={$(#=Date(MakeDate(1900, MeseOb,1), 'MMM'))}>}qta_ven)



                • SET Analysis - date filter
                  Neil Miller

                  Yes, I think you're stuck with the variable being a number. My suggestion was to use your Load script to load "mese_chius" in as a number too. Using the INLINE suggestion in that other post, you could link "mese_chius" to an INLINE table that would add a month number to your dataset.

                  INLINE [

                  mese_chius, mese_chius_num

                  Feb, 2 ....

                  That way you would have the month name and the month number as separate fields and could use whichever one suited the situation.

                  In order to attach a file, click the Options tab while composing your message and use the Add/Attach button. All you would need is a really simple sample. Just something that would use your field names and show what format your data is in. I don't have any sample data that stores a month name, so I don't have anything to test on. When doing Set Analysis, I usually end up doing guess and check a few times until I hit the correct formula.

              • SET Analysis - date filter
                Rob Wunderlich


                Flavio wrote:
                It doesn't work because the $ replacement puts a NUMBER within brackets as a selection


                The # in the variable expression forces a number. Have you tried it without the #?


                  • SET Analysis - date filter
                    Consulting nn

                    THANKS, Rob, YOU ARE RIGHT!!!! I copied acritically a sample with the # ... without it the "turn-around solution" goes right and the formula WORKS and I can proceed. Sum ({$<mese_chius={$(=Date(MakeDate(1999, MeseOb,1), 'MMM'))}>}qta_ven)

                    Just for the sake of pure KNOWLEDGE and to answer to other advice, we are speaking of a local variable attached to a slider. My initial question was why would the IF clause work and the same field would not produce the same result within a SET instruction (that needs the "date conversion" function that was not working for the # sign)?

                    The (mese_chius = MeseOb) is evaluated differently from <mese_chius={$(#MeseOb)}> With or without the #sign before MeseOb a number is substituted and it expects MMM text (?).

                    Managing the month number month text (MM MMM) with an INLINE structure is surely one way to avoid implicit conversion.


                    • SET Analysis - date filter
                      Shumail Hussain

                      Dear Rob,

                      I have similar kind of issue. I am trying to get the report date which is the last month end date i.e. '09/30/2010'. I put this value in the variable throught the same table where all the Month end date present. please see the below code.

                      LOAD Report_NO, LoadDate_Active, LoadDay_Active, bal, AIF FROM [$(Outputpath)CardMast_Active.qvd] (qvd);

                      minstring(LoadDate_Active) as StartDate,
                      maxstring(LoadDate_Active) as ReportDate
                      resident CardMast_Active

                      // now get the values...
                      let ReportDate = peek('ReportDate',0) ;

                      =COUNT({$<AIF = {'P'}, LoadDate_Active = {$(=Date($(#=ReportDate),'MM/DD/YYYY'))} >} AIF)

                      The above expression is not working.. Tongue Tied please help