10 Replies Latest reply: Jun 14, 2017 10:54 AM by Todd Buss RSS

    syntax issue in set analysis

    Todd Buss

      Can anyone tell me why this set analysis works:

       

      Count( {$<ReportDate={"$(=(Max(ReportDate)))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

       

      and this one doesn't?:

      Count( {$<ReportDate={"$(=(Max(ReportDate)-3))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

       

      The Qlik syntax is a necessary evil...but very evil.

       

      Thanks.

        • Re: syntax issue in set analysis
          Stefan Wühl

          Assuming that your ReportDate format is e.g. 'M/D/YYYY' and that ReportDate field shows indeed a value like requested by the modifier (double check for example that you are not coping with timestamps instead of dates), you can try just formatting the modifier value:

           

          Count( {$<ReportDate={"$(=Date(Max(ReportDate)-3,'M/D/YYYY'))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])



          Dates in Set Analysis

          • Re: syntax issue in set analysis
            Jahanzeb Hashmi

            I think this should work

             

            Count( {$<ReportDate={"$(=(=(Max(ReportDate)-3)))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

            • Re: syntax issue in set analysis
              Gerardo Alegria Benitez

              Try with:

               

              Count( {$<ReportDate={"$(=(Max(num(ReportDate))-3))"}>*<[Inpatient Bed]={"Inpatient"}>}[Bed ID])

              • Re: syntax issue in set analysis
                Sunny Talwar

                This is a weird behavior which I have witnessed as well in the past... Lets look at this simple example

                 

                SET DateFormat='M/D/YYYY';

                 

                Table:

                LOAD Date(MonthStart(Today() + IterNo()), 'YYYY-MM-DD') as MonthStart,

                  Ceil(Rand() * 1000) as Sales

                AutoGenerate 1

                While IterNo() <= 1000;

                 

                Here you will see that the default date format is M/D/YYYY, but I have used YYYY-MM-DD for MonthStart. Now when I calculated Max(Monthstart) I see this

                 

                Capture.PNG

                 

                It keeps the format of Monthstart. But now if I do this -> =Max(MonthStart) - 3, I am seeing this

                 

                Capture.PNG

                 

                Now set analysis is very format sensitive and you need to make sure that format is not lost. So, in this case since the MonthStart is formatted as YYYY-MM-DD, we need to use Date function to convert it back to this format.

                =Date(Max(MonthStart) - 3, 'YYYY-MM-DD')

                • Re: syntax issue in set analysis
                  Todd Buss

                  Thank you all for your help on this.  I overestimated my skill and I simplified my code to spare everyone all the noise.  My formula still doesn't work.  With the added date formatting everyone suggested, my real-world code looks like this:

                   

                  Count(DISTINCT

                  {<ReportDate =

                  {"$(='=' &

                  date(Max(ReportDate)-weekday(Max(ReportDate),4),'M/D/YYYY') )"},

                  [DC #]

                  = e({<ReportDate = {"$(='=' &

                  date(Max(ReportDate)-weekday(Max(ReportDate),4)-7),'M/D/YYYY')"}>})>}

                  [DC #])

                   

                  //date(Max(ReportDate)-weekday(Max(ReportDate),4))  //  = latest friday

                  //date(Max(ReportDate)-weekday(Max(ReportDate),4)-7) //  = friday before

                   

                  Sunny, you may recognize the origin of this problem.  :-).  The goal is to count the discrepancies between reports from two specific dates.   Later on, I hope to insert variables instead of dates, and then use recent fridays as default dates in the user prompts. 

                    • Re: syntax issue in set analysis
                      Stefan Wühl

                      Why are you adding a equal sign before the date? This will indicate an advanced / expression search and is not what you want.

                       

                      To debug your expression, put it as expression in a straight table without an expression label (and ReportDate as dimension). Now, when hovering over the label, you see the expression with all dollar sign expansions expanded.

                      What do you see?

                        • Re: syntax issue in set analysis
                          Todd Buss

                          Yes, Stefan, that was some leftover clutter that I thought was harmless.  Here's my attempt to clean it up:

                           

                          Count(DISTINCT

                          {<ReportDate ={"$(date(Max(ReportDate)-weekday(Max(ReportDate),4),'M/D/YYYY') )"},[DC #]

                          = e(

                          {<ReportDate = {"$(date(Max(ReportDate)-weekday(Max(ReportDate),4)-7),'M/D/YYYY')"}>})>}[DC #]

                          )

                           

                          //date(Max(ReportDate)-weekday(Max(ReportDate),4))  //last friday

                          //date(Max(ReportDate)-weekday(Max(ReportDate),4)-7) //friday before

                           

                          I don't see anything on hover-over, and my result is now zero.

                            • Re: syntax issue in set analysis
                              Sunny Talwar

                              You do need an equal... but not 2 equals (is what Stefan might have wanted to say)

                               

                              Count(DISTINCT {<ReportDate = {"$(=Date(Max(ReportDate)-weekday(Max(ReportDate),4),'M/D/YYYY') )"}, [DC #] = e({<ReportDate = {"$(=Date(Max(ReportDate)-weekday(Max(ReportDate),4)-7),'M/D/YYYY')"}>})>} [DC #])

                              • Re: syntax issue in set analysis
                                Stefan Wühl

                                Ah, you are using Qliksense, the mouse over works in QlikView.

                                 

                                If you want to evaluate an expression in a dollar sign expansion, you need one equal sign:

                                 

                                ReportDate ={"$(=date(Max(ReportDate)-weekday(Max(ReportDate),4),'M/D/YYYY') )"}


                                I was talking about the '='&... part.

                          • Re: syntax issue in set analysis
                            Todd Buss

                            Thanks to all.

                            After toying with the formula for a bit, I discovered that the problem on my end was the date format has to be:  'MM,DD,YYYY' .  Everything is working now.