13 Replies Latest reply: Jun 21, 2016 10:29 PM by Haikuo Yu RSS

    Could Set Analysis be applied to dimension?

    Haikuo Yu

      Could Set Analysis be applied to dimension? If so, how to do it? Could you please give one example?

      Or could Set Analysis be only applied to measure?

      Thanks!

        • Re: Could Set Analysis be applied to dimension?
          Sunny Talwar

          You can apply set analysis using Aggr() function. For instance you want to see items based on category = x, you can try this:

           

          Aggr(Only({<Category = {'x'}>} Item), Item)

          • Re: Could Set Analysis be applied to dimension?
            Stefan Wühl

            You can do this if you use advanced aggregation to allow aggregation functions in the calculated dimension, though for simple filtering of dimensional values, it might be easier to write:

             

            If(Category = 'x', Item)

             

            But there are settings were it's useful to have the set analysis option also in a calculated dimension / field expression in a list box.

              • Re: Could Set Analysis be applied to dimension?
                Haikuo Yu

                Thanks so much for your help!

                My question is like this. I have a table with 3 columns, state, region, and sale_value. I need to show the map with state. I also need to set the region of the map with different color. At the same time, I need use region as a filter.

                I use state as my dimension, and I created map color with expression:

                If ( Region= 'west', red(),

                      If ( Region = 'east', green(),

                           ( 

                            if ( Region = 'south', blue(),

                                if (Region = 'north', yellow(), cyan())

                               )   

                           )

                      )

                )

                I also created a measure and use Set Analysis.

                My problem is that I can not create map color (for region) and put it on top of the map.

                the map color legend (for region) is not interactive.

                Could you please give any suggestion?

                 

                • Re: Could Set Analysis be applied to dimension?
                  Haikuo Yu

                  My table has two columns, myDate and myValue. I use the table to create a bar chart. I would like to use Set Analysis to show the default date range between the first day of current year and current date.

                  I use the following Set Analysis, but it does not work. Could you please give any suggestion? Thanks!

                  Sum( {$ <  date(myDate)> makedate(year(today())) and date(myDate)< today() > } myValue)

                    • Re: Could Set Analysis be applied to dimension?
                      Stefan Wühl

                      Try this, assuming myDate format of 'MM/DD/YYYY' or adapt the format accordingly:

                       

                      Sum(

                      {$ <  myDate = {">=$(=Date(Yearstart(Today()),'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"} >}

                      myValue)

                       

                      only field names allowed left side of a field modifier's equal sign (no functions). See also

                      A Primer on Set Analysis

                      Why is it called Set Analysis?

                      Dates in Set Analysis

                        • Re: Could Set Analysis be applied to dimension?
                          Haikuo Yu

                          Swuehl, thanks so much for your help! I have a better understanding of Set Analysis Syntax now.

                          Your suggestion works well to set a default date range (fist date of current year to current date), however I still have some problem and would like to ask for your help.

                          I also use myDate as a filter (there is a default date range, and also I need to select other date range except default date range, for example, myDate starts from 2014)., When the default date range works, while the filter with myDate does not work any more.

                          Could you please give any suggestion?

                            • Re: Could Set Analysis be applied to dimension?
                              Stefan Wühl

                              That's because your selection in myDate will be overwritten by the set modifier on the same field.

                               

                              You can modify the above expression to

                               

                              Sum(

                              {$ <  myDate *= {">=$(=Date(Yearstart(Today()),'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"} >}

                              myValue)

                               

                              Note the intersection operator *

                               

                              now the field modifier will create a set made of the intersection between the previous default range and user selections (still, if the user selects last year, this will render the expression to return zero).

                               

                              Another option would be to use a conditional statement, like If() to check for user selections using GetSelectedCount( myDate):

                               

                              =If(GetSelectedCount(myDate), Sum (myValue), Sum( {<....>} myValue) )

                                • Re: Could Set Analysis be applied to dimension?
                                  Haikuo Yu

                                  If(GetSelectedCount(myDate), Sum (myValue),  Sum({$ <  myDate *= {">=$(=Date(Yearstart(Today()),'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"} >} myValue)    )

                                  works perfectly.

                                  Swuehl, thanks so much for your help! Great to learn from you, and hopefully this post will be also helpful to other Qlik members.Thanks again!

                                  One last question about this topic. Finally, I need to load data from Oracle table. the format of myDate is 07-JAN-14 at Oracle table.In Qlik Data Load editor:

                                  SET DateFormat='M/D/YYYY';

                                  SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

                                  SET MonthNames='1;2;3;4;5;6;7;8;9;10;11;12';

                                   

                                  My problem is that when I load myDate to Qlik, and then its format will become 2015-08-06 00:00:00.0000

                                   

                                  I use:

                                  Load *,

                                        date(myDate) as myDateFormat

                                  From

                                       myJoinedTable

                                  Order by myDate

                                   

                                  And then I use myDateFormat as filter, however, when I use the following,

                                  If(GetSelectedCount(myDateFormat), Sum (myValue),  Sum({$ <  myDateFormat*= {">=$(=Date(Yearstart(Today()),'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))"} >} myValue)    )

                                  the date is not in order (seems random order)

                                   

                                  I also try to create a dimension myDateDimension = date(myDate) and put my myDateDimension into above script, it does work.

                                   

                                  I also try to order date(myDate) in Qlik load by referring the following, it does not work either.

                                  Can you use 'Order By' in a QVD Load?

                                   

                                  myTemp:

                                  Load  *,

                                  date(myDate) as myDateFormat

                                  from myJoinedTable;

                                   

                                  myNewData:

                                  Load  *

                                  Resident myTemp Order By myDateFormat;

                                   

                                  Drop table myTemp;

                                   

                                  (myNewData table does not load into Qlik)

                                   

                                  Could you please give any suggestion? Thanks so much!

                                    • Re: Could Set Analysis be applied to dimension?
                                      Stefan Wühl

                                      For this:

                                       

                                      myTemp:

                                      Load  *,

                                      date(myDate) as myDateFormat

                                      from myJoinedTable;

                                       

                                      myNewData:

                                      Load  *

                                      Resident myTemp Order By myDateFormat;

                                       

                                      Drop table myTemp;


                                      Here myNewData will be autoconcatenated to the myTemp table, you need to use:

                                       

                                      myTemp:

                                      Load  *,

                                      date(myDate) as myDateFormat

                                      from myJoinedTable;

                                       

                                      myNewData:

                                      NOCONCATENATE

                                      Load  *

                                      Resident myTemp Order By myDateFormat;

                                       

                                      Drop table myTemp;

                                       

                                      W.r.t your date issue,could you upload a sample application with just your date field loaded?

                                        • Re: Could Set Analysis be applied to dimension?
                                          Haikuo Yu

                                          Swuehl, thanks so much for your help!

                                          NOCONCATENATE works to load my new table (myNewData; myNewData will be autoconcatenated to the myTemp table; after we use  NOCONCATENATE , new table will not be dropped any more )

                                          However, after I use [date(myDate) as myDateFormat; Resident myTemp Order By myDateFormat;], date is still not in order at Qlik. When I use date as dimension to create chart at Qlik, the date at dimension is still not in order (seems random). Any suggestion is appreciated!

                                          I will be sure to update a sample file of my date problem.

                                          Thanks!

                                          • Re: Could Set Analysis be applied to dimension?
                                            Haikuo Yu

                                            Swuehl, thanks so much for your help!

                                            I am still struggling about this problem, and I do not forget to update some sample file. I will be sure to do, and sorry for being late.

                                            I use the following to plot a accumulative chart and could put the date in order now. (I label the x axis with start/end day of each week)

                                             

                                            If(GetSelectedCount(myDate),

                                                RangeSum ( above ( Sum(myValue) , 0 , RowNo() ) ) ,

                                               

                                                RangeSum ( above ( Sum({< myYear *= {">=2016" } >} myValue) , 0 , RowNo() ) )

                                              )

                                            I also uncheck "show null value" and "show zero value" at Qlik sense.

                                            However, the problem is that the 0 zero is still at chart. I would like to only keep the non-zero value/plot on chart.

                                            Could you please give any suggestion?how to hide 0 value point.JPG