6 Replies Latest reply: May 25, 2009 10:52 AM by Jacobo RSS

    Dynamic variables

    Jacobo

      Hi all,

      Here is my problem, when i enter on a sheet, i create some variable depending on some possible values of a field. The name of the variables i create is "var_" + CUSTOMER, being CUSTOMER the field. Then i assign to all the variables a value. Until there, all goes good. Now i want to made an expression on a pivot table, with the dimension CUSTOMER, where it shows the value of the variable depending on the value of the CUSTOMER. The expression would be like this if it was right:

      =("var" + CUSTOMER)

      where ("var" + CUSTOMER) would be the corresponding variable depending on CUSTOMER value.

      My problem isn't 100% that, the situation is a bit complicated, but what i need is basically to access the corresponding variable depending on the dimension value. I'm using version 7.5 to Qlikview, but if its possible to do in 8.5 would be usefull too.

       

      Thanks beforehand!

        • Dynamic variables
          ewasserman

          I am not sure that is possible, based on my research. Can you present the larger problem, there may be another solution that would solve it.

            • Dynamic variables
              Jacobo

              Thank for your effort Eugene. It seems what i want is not possible, and its a pitty because i think it's a very usefull option. Anyway ill explain more in detail my problem and maybe find another way to solve the problem:


              *** We have 3 table:

              - SALES_SELLOUT: Table containing the sales of a product in a month from some store.

              - PRODUCTS: Table containing all the info about products, and related with SALES_SELLOUT.

              - MONTHS: Table containing all the months from the first month in SALES_SELLOUT to the last month, even if there is no sale in the month. This table is NOT related with other tables.

              *** There is 2 variables:

              - varMinDATE: Stores the MIN date selected from the table MONTHS ---> =DATE(MIN(MONTHS.DATE),'DD/MM/YYYY')

              - varMaxDATE: Stores the MAXdate selected from the table MONTHS ---> =DATE(MAX(MONTHS.DATE),'DD/MM/YYYY')

              *** There is 1 filter:

              - For the field MONTHS.DATE

              *** There is one pivot table with some dimensions from SALES_SELLOUT and PRODUCTS tables (isn´t important since they are related). And there is one expression with this:

              SUM(
              IF(
              SALES_SELLOUT .DATE >= varMinDATE
              AND SALES_SELLOUT.DATE <= varMaxDATE
              , SALES_SELLOUT.VOL
              )
              )

              At this point, when you select some months in the MONTHS.DATE filter, the pivot table shows the Volume for the sales included in that period of time. Because other tables on the document, i can't use the SALES_SELLOUT.MONTH as the filter, that's why i use another table to filter the months.

              The problem comes here, now i want that the pivot table just shows me the Volume of the sales in that period of time but just from the stores that have sales in ALL the months selected in the filter. So if you select from january'09 to march'09, the stores that didn´t have sales on those 3 months, won´t be used to calculate the Volume.

              My first approach to solve the problem was to make another table with all DISTINCT STORES, MONTH from the SALES_SELLOUT table, and related with the MONTHS table, so the fields from the new table were: AUX.STORE,AUX.DATE and MONTHS.DATE. So when you select the months in the filter, the table will have 1 line for every store that has sales in the months selected. Lets suppose Store A has sales all the months, B also has sales all the months, and C has sales all the months except march'09. Then we select in the months filter from jan'09 to april'09. In the new table there will be 4 lines for store A (jan,feb,mar,apr), 4 lines for store B (jan,feb,mar,apr), and 3 lines for C (jan,feb,apr).

              The next step was to change the filter on the expression, but there was where Qlikview stopped me. The perfect expression would be:

              SUM(
              IF(

              COUNT(DISTINCT IF(AUX.STORE = SALES_SELLOUT.STORE, AUX.DATE) ) = COUNT(TOTAL DISTINCT MONTHS.DATE)
              AND SALES_SELLOUT .DATE >= varMinDATE
              AND SALES_SELLOUT.DATE <= varMaxDATE
              , SALES_SELLOUT.VOL
              )
              )

              The problem is Qlikview just let me do this if i put TOTAL on the COUNT, to end like this:

              COUNT(TOTAL DISTINCT IF(AUX.STORE = SALES_SELLOUT.STORE, AUX.DATE) ) = COUNT(TOTAL DISTINCT MONTHS.DATE)

              But that will count all the distinct AUX.DATES, without taking in consider the store. I tried some variants with same result, and then was when i started with variable shot.

              I hope this clarify you a bit more Eugene, and maybe iluminate you with some solution.

              Thank beforehand!

            • Dynamic variables

              Try this : $(='var_'&CUSTOMER)

              and watch the attached example !

                • Dynamic variables
                  Jacobo

                  Hi Emontant,

                  First thank for your answer. I've been doing some tests with it in QV 7.5 and it doesn't work, so i tried on QV 8.5, and the example you attached works fine, but it has one problem. Use $(='var_'&CUSTOMER) just works if CUSTOMER has 1 value selected, so if i select 'TATA' and 'TITI' the expression shows nothing. To try it i've created a table with CUSTOMER as dimension and in the expression i used $(='var_'&CUSTOMER). What i need is that the expression works also when multiples values are selected for CUSTOMER.

                  I think what i need isn't possible in Qlikview, anyway what you told me seems to be very usefull in other situations, so thanks a lot for the information!

                   

                  P.D.: I attached your QV example with the table i used to test the dynamic variables. Please if you have a moment check it in case i did something wrong and it really works with multiple values selected on CUSTOMER.

                • Dynamic variables
                  ewasserman

                  Can you not use the same solution as was posted to your other query?

                  http://community.qlik.com/forums/p/16611/65016.aspx

                  I have attached a similar file with Store as the main dimension.

                    • Dynamic variables
                      Jacobo

                      Hi Eugene,

                       

                      Yeah, it works for my other post too. I was trying to reach the same goal on both posts, but from diferent ways. Anyway at the end i had to make it other way because i had some problems with the QV version. I'll try to change to this solution when i was able to update the version.

                      Thanks for your time and effort ^^, hope i can help you whenever you have some problem.