12 Replies Latest reply: Aug 28, 2012 2:59 PM by Jose Tos RSS

    Show excluded and not excluded values

    Jose Tos

      Hi,

       

      I have one problem to solve and this is the case:

       

      I have to show a table with 3 dimensions and 4 expressions, doesn´t matter the calculations, but the user selects one Stock Date that it´s not showing at the table, it´s only used as filter, so the table shows information about data that has relation with that Stock Date but I need to show also the excluded data that has no realtion with that Stock Date beacuse one expression returns the same value having relation or not with Stock Date and users wants to see all at the same time.

       

      I was trying to use functions p() and e() but this only works as far as I know if I use the same dimension that I´m selecting and only with one simple expression, something like this:

       

      Dimension in tha table: Stock Date

      Actual Selection: Stock Date

       

      expression: sum({ 1-$<[Stock Date] = p([Stock Date]) >} AnyValue)

       

      In my table, [Stock Date] is not a dimension, I have other dimensions and I use set analysis in the expressions because I´m calculating values since 12 months before the [Stock Date] selected.

       

      Any help will be apreciated.

       

      Regards,

      Chema

        • Re: Show excluded and not excluded values
          whiteline _

          You can use any dimension in set analysis weather it's used in a chart or not.

           

          Suppose you have some complex expression: SUM({$<Field1={"Condition1"}, Field2={"Condition2"},...>} Value)

          If you want to calculate the same expression over the excluded data you can use this:

          SUM({1-$<Field1={"Condition1"}, Field2={"Condition2"},...>} Value)

           

          But if you want to invert your selection with the same logic you should keep all other conditions:

          SUM({1<Field1={"Condition1"}, Field2={"Condition2"},...>-$<Field1={"Condition1"}, Field2={"Condition2"},...>} Value)

           

          Finally, if you want to invert only one field [Stock Date] and keep all other conditions you should use function e().

          SUM({$<[Stock Date] = e([Stock Date]), Field1={"Condition1"}, Field2={"Condition2"},...>} Value)

            • Re: Show excluded and not excluded values
              Jose Tos

              Hi whiteline,

               

              This is my expression, I've tried your solution but still not working

               

              aggr(

                        NODISTINCT sum({<[Fecha_Stock] = e([Fecha_Stock]),

                        Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0))

                        ,[ROT.Mes Año]=,ROT.Mes=,ROT.Año=,Centro_Rotacion=,ROT.Fecha=>} [Cantidad_real]),           Componente_STOCK

                      )

              /

              1000

               

              Have you ever used the expression? Am I missing something?

               

              Thanks

                • Re: Show excluded and not excluded values
                  whiteline _

                  Lets consider only your Sum expression:

                   

                  sum({<[Fecha_Stock] = e([Fecha_Stock]),

                            Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0)),

                            [ROT.Mes Año]=,

                            ROT.Mes=,

                            ROT.Año=,

                            Centro_Rotacion=,

                            ROT.Fecha=>} [Cantidad_real])

                   

                  Suppose the user selected some [Stock Date].

                   

                  If you want to calculate exactly that sum over all other values of [Stock Date], try this.

                   

                  sum({<[Fecha_Stock] = e([Fecha_Stock]),

                            Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0)),

                            [ROT.Mes Año]=,

                            ROT.Mes=,

                            ROT.Año=,

                            Centro_Rotacion=,

                            ROT.Fecha=,

                            [Stock Date]=e([Stock Date])>} [Cantidad_real])

                    • Re: Show excluded and not excluded values
                      Jose Tos

                      Sorry, [Stock Date] is [Fecha Stock] in my expression, I traduced the field at the beginning.

                       

                      The case is this:

                      I have only one [Fecha Rotacion] in a table that has only information about stock of that day. This table has relation with other table that has more information, so I join [Fecha Rotacion] with this table renaming as [Fecha Stock], so I have [Fecha Stock]= [Fecha Rotacion] and [Fecha Stock]= null for all the components that doesn't have stock at that day.

                       

                      In a straight table I have all I need but only with components that have stock at [ [Fecha Stock] | [Fecha Rotacion] ],

                      and as you know, I want the same but with components that have no stock at that day.

                       

                      I don´t know if the explanation helps, but this is it.

                       

                      Regards

                        • Re: Show excluded and not excluded values
                          whiteline _

                          Ok. Now I understand.

                           

                          You want not to just  invert selection but to include all data that has [Fecha Stock]=null.

                          e([Fecha Stock]) function inverse it like ListBox, so that you skip all [Fecha Stock]=null.

                           

                          So you should use second solution from my first post.

                          First constract set with all [Fecha Stock] and then subtract the set with selected [Fecha Stock].

                           

                          sum({1<Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0)),

                                             [ROT.Mes Año]=,

                                             ROT.Mes=,

                                             ROT.Año=,

                                             Centro_Rotacion=,

                                             ROT.Fecha=>

                                  -$<[Fecha_Stock] = e([Fecha_Stock]),

                                             Inicio_Mes={'>=$(=MonthStart(AddMonths(Fecha_Rotacion,-11)))<=$(=AddMonths(Fecha_Rotacion,0)),

                                             [ROT.Mes Año]=,

                                             ROT.Mes=,

                                             ROT.Año=,

                                             Centro_Rotacion=,

                                             ROT.Fecha=>} [Cantidad_real])

                            • Re: Show excluded and not excluded values
                              Jose Tos

                              Nothing happens.

                               

                              The problem is when I select a [Fecha Stock], all my expressions will be about possible components so I can´t show excluded components.

                              I'm able to make calculations with excluded data but I can´t show excluded dimensions data.

                              I'm screwed!!

                                • Re: Show excluded and not excluded values
                                  whiteline _

                                  Dimensions qlikview will show  if your expression has a value.

                                   

                                  Play with the attached file.

                                    • Re: Show excluded and not excluded values
                                      Jose Tos

                                      If you select FullDate

                                      Sum ({$<Date=e(Date)>}Value) returns nothing.

                                       

                                      So is like I was saying in my first post:

                                       

                                      "

                                      I was trying to use functions p() and e() but this only works as far as I know if I use the same dimension that I´m selecting and only with one simple expression, something like this:

                                       

                                      Dimension in tha table: Stock Date

                                      Actual Selection: Stock Date

                                       

                                      expression: sum({ 1-$<[Stock Date] = p([Stock Date]) >} AnyValue)

                                      "

                                      In the attached file is the same but with Date field instead of Stock Date

                                    • Re: Show excluded and not excluded values

                                      I have not really been following this discussion, but I do remember something that might help you. I was reading somewhere that said that in set if you put ={} you will return only the nulls for that field.

                                      So I would assume that putting [Fecha Stock]={} somewhere in your set will show you the null [Fecha Stock] values.

                                       

                                       

                                      Update:

                                       

                                      Found it:

                                      Empty element sets, either explicitly e.g. <Product = {}> or implicitly e.g. <Product = {"Perpetuum

                                      Mobile"}> (a search with no hits) mean no product, i.e. they will result in a set of records that are not associated

                                      with any product. Note that this set cannot be achieved through usual selections, unless a selection is

                                      made in another field, e.g. TransactionID.