14 Replies Latest reply: Oct 24, 2016 3:13 PM by Sunny Talwar RSS

    Modelling, Calculated values

    Ganesh Bagavathi

      Hi All,

       

      I have been struggling to find a solution for a problem which is explained in the attached QVW file here.

      It is something to do with calculating and storing values by ignoring few joins. Please suggest me a way to resolve this.

       

      Thanks.

        • Re: Modelling, Calculated values
          Sunny Talwar

          May be you can do like this in your expression:

           

          =Sum({<FISC_YR_NBR={"$(=vFXPrevYrToday)"}, CTRY_NAM, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

           

          =Sum({<FISC_YR_NBR={"$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}FXDayOfYearToday))"}, CTRY_NAM, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

           

          So, basically ignore selection in Country field and allow currency to drive the expressions.

           

          Capture.PNG

            • Re: Modelling, Calculated values
              Ganesh Bagavathi

              Thanks for your solution Sunny, this is what i was looking for.

              So if i want to change the Dimension to a different column from a different table, what should be done?

              The values changes back to 23.9161 for Australia if the FISC_MTH_NBR is pulled from the DT_CONS_DIM table.

               

              DT_CONS_DIM.FISC_MTH_NBR Sum1 Sum2 Days1 Days2
              273.6691 221.1322 35 35
              323.916123.91613535
              420.16120.1612828
              519.722819.72282828
              622.348322.34833131
              187.5209134.9843535

               

              My intention is to get the Sum1, Sum2 values from the BB_CCY_EXCH_DIM table , but the table or graph should be plotted against the Fisc_Month_Number from DT_CONS_DIM table.

              I could not find a way to avoid it, thats the reson i have included the FISC_MTH_NBR and other columns in the BB_CCY_EXCH table.FXN_Forum.JPG

            • Re: Modelling, Calculated values
              Ganesh Bagavathi

              Hello,

              I am trying to find a way to calculate values for Year To date, QTD and MTD. Requirement is below,

               

              YTD: (Should be displayed in a Text box as a standalone value)

              (Sum of Revenue of every Month)*(Average of Exchange of every month)

              Average of Exchange of every month = (Sum of Exchange rates)/(No of days in that month)

               

              What is already done:

              If i build a table with Month as the dimension, i will be able to get the values, however if i remove it, everything rolls up which will not be the desired value for me.

               

              MonthRevenueSum of Exch ratesNo of DaysAverageFinal Value
              1022.6158280.80770
              2021.8186280.38960
              312613425127.0161350.296997361587
              410236649621.7015280.182479339518
              59764520722.1012280.150377074152
              615875624626.9398350.1480122196043
              7020.6823280.09850
              8020.5046280.08620
              914422139024.7217350.0906101868513
              1016162313.7533190.0471116992

               

              What needs to be done:

              I need suggestions to achieve the below,

              YTD  Final value should be in a single cell, NOT in a table format.

              And it should be calculated as Sum of Monthly "Final value", which is Calculating Monthly Averages and multiplying them with Revenue every month and at the end, all the Final values has to be summed up.

               

              Similar to this, the QTD should also be done.

              Attached a sample application with this, please let me know if more details are needed.

               

              Thanks.

                • Re: Modelling, Calculated values
                  Sunny Talwar

                  Are these the numbers you are hoping to see?

                   

                  Capture.PNG

                    • Re: Modelling, Calculated values
                      Ganesh Bagavathi

                      Thanks a ton Sunny, I was trying out the Aggr but missed to place it at the place like you have done.

                      YTD is perfect, and QTD i have made it to perfection now.Just that we will have to choose a different Start date for Quarter, which you might not have a possibility of knowing.

                       

                      Thanks again

                        • Re: Modelling, Calculated values
                          Sunny Talwar

                          Not a problem Ganesh -

                           

                          If you got all the things you were looking for, I would suggest closing this thread by marking correct and any helpful responses.

                          Qlik Community Tip: Marking Replies as Correct or Helpful

                           

                          Best,

                          Sunny

                            • Re: Modelling, Calculated values
                              Ganesh Bagavathi

                              Getting into multiple issues when i explore more on this.

                              Now if i bring in another dimension and try to plot the values against them, it is not giving me the right numbers.

                               

                              Set expression being used: (Please ignore the first two "if" conditions as they are working as expected.)

                               

                              =Num(if(vCurr='USD', Sum({<PTY_VEND_SUP_GP_DES=,PTY_VEND_GP_DES=,PTY_VEND_SUB_GP_DES=,FISC_YR_NBR={"$(=Only({1}YearofToday))"},DateNum={">=$(=Num(FISC_YR_START_DT))<=$(=Num(Today()))"}>} PRC_USD_AMT)

                                ,if(vCurr='LC',Sum({<PTY_VEND_SUP_GP_DES=,PTY_VEND_GP_DES=,PTY_VEND_SUB_GP_DES=,FISC_YR_NBR={"$(=Only({1}YearofToday))"},DateNum={">=$(=Num(FISC_YR_START_DT))<=$(=Num(Today()))"}>} PRC_LCY_AMT)

                                ,if(vCurr='FXN',Sum(Aggr(Sum({<FISC_YR_NBR={"$(=Only({1}YearofToday))"},DateNum={">=$(=Num(FISC_YR_START_DT))<=$(=Num(Today()))"}>} PRC_LCY_AMT)

                              *(Sum({<FISC_YR_NBR={"$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}FXDayOfYearToday))"},CTRY_NAM, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

                                /Max({1<FISC_YR_NBR={"$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR ={"<=$(=Only({1}FXDayOfYearToday))"}>}FISC_DAY_OF_MTH_NBR)), FISC_MTH_NBR))))),'#,###,##0')

                               

                              Top10 FXn Forum.JPG

                               

                              The list should look like the above image, with a list of names, but it gets reduced like below.

                              Top10 FXn Forum1.JPG

                              Even though the list has shrunk, the Total values are matching with the YTD values. And the 'Others' value is always zero.

                               

                              The names are from a new dimension table and it is connected to the SLS_RPT_TRNS table with a key value.Sorry, i could not include the new dimension in a sample here. Please let me know if you need more information.

                                • Re: Modelling, Calculated values
                                  Sunny Talwar

                                  Not sure I understand it completely, but have you add your new dimension to the Aggr() function? Any dimension you add to your chart, add it to your Aggr() function and the fact that you are using some if statements could make the mismatch even more bigger if you don't add all your dimensions to the Aggr() function.

                                    • Re: Modelling, Calculated values
                                      Ganesh Bagavathi

                                      Attaching the sample qvw file. I have included the new dimension into the AGGR expression. But the Sum of rates is also chaning if i pull in everything into a table with the new dimension. Let me validate the backend of data.

                                      Top10 FXn Forum3.JPG

                                      Found out the issue.The Rate amount is not getting calculated independent of the new dimension. It is getting related to the New dimension and its entries in the Fact, so it is filtered out. The difference in value is because of that, i am trying out the below Set expressions but no luck yet.

                                      How do i exclude the dimension even if i am using the dimension in my chart/table?

                                      if(vCurr='FXN',Num(Sum(Aggr(Sum({<FISC_YR_NBR={"$(=Only({1}YearofToday))"},DateNum={">=$(=Num(FISC_YR_START_DT))<=$(=Num(Today()))"}>} PRC_USD_AMT)

                                      *(Sum({<FISC_YR_NBR={"$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}FXDayOfYearToday))"},PTY_VEND_GP_DES, CTRY_NAM, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

                                        /Max({1<FISC_YR_NBR={"$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR ={"<=$(=Only({1}FXDayOfYearToday))"}>}FISC_DAY_OF_MTH_NBR)),PTY_VEND_GP_DES, FISC_MTH_NBR)),'#,###,###,##0')))

                                        • Re: Modelling, Calculated values
                                          Sunny Talwar

                                          What is the output that you are expecting to see? Can you elaborate on this?

                                            • Re: Modelling, Calculated values
                                              Ganesh Bagavathi

                                              I have uploaded the sample application with added dimensions, the numbers when USD or FXn buttons are selected should be the same in the 'Actual' table. My Set expressions for PY which is Prior Year is very similar to the Current Year CY.

                                               

                                              Top10 FXn Forum4.JPG

                                                • Re: Modelling, Calculated values
                                                  Sunny Talwar

                                                  This expression seems to have worked for me

                                                   

                                                  =Num(Sum(Aggr(if(vCurr='USD',

                                                  Sum({<FISC_YR_NBR={"$(=(vPrevYrToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}DayOfYearToday))"}>} PRC_USD_AMT),

                                                    if(vCurr='FXN',Sum(Aggr(Sum({<FISC_YR_NBR={"$(=(vPrevYrToday))"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}DayOfYearToday))"}>} PRC_LCY_AMT)

                                                  *(Sum(TOTAL<FISC_MTH_NBR>{<FISC_YR_NBR={"$(=vFXPrevYrToday)"},FISC_DAY_OF_YR_NBR={"<=$(=Only({1}FXDayOfYearToday))"},

                                                    CTRY_NAM,PTY_VEND_SUP_GP_DES,PTY_VEND_GP_DES,LOB_LVL1_DES,LOB_LVL2_DES,LOB_LVL3_DES, FROM_CCY_CD = p(FROM_CCY_CD)>}RATE_AMT)

                                                    /Max(TOTAL<FISC_MTH_NBR>{1<FISC_YR_NBR={"$(=vFXPrevYrToday)"}>}FISC_DAY_OF_MTH_NBR)),

                                                    PTY_VEND_SUP_GP_DES,PTY_VEND_GP_DES,LOB_LVL1_DES,LOB_LVL2_DES,LOB_LVL3_DES, FISC_MTH_NBR)))), PTY_VEND_GP_DES)),'#,###,###,##0')

                                                   

                                                  Capture.PNG

                                                    • Re: Modelling, Calculated values
                                                      Ganesh Bagavathi

                                                      The numbers when we switch between USD and FXn should be the same for the country "US" and it is working as expected for the CY column. Please refer the screens below, the CY column will not change if we switch between USD and FXN which is right. It is deviating only for the PY column here. Moreover what will happen if i introduce a cyclic dimension to this table? Should it br grouped on all the different dimension of the group?

                                                      Thanks in advance Sunny.

                                                       

                                                      FXN Forum Qn1.JPG

                                                      FXN Forum Qn1.1.JPG

                                                        • Re: Modelling, Calculated values
                                                          Sunny Talwar

                                                          So this is more like an issue with your current expression in the straight table also? Because based on my selection in USD OR FXn, I am seeing the numbers changing in the straight table also. Which of the two numbers are the correct numbers? 7,900,... or 8,035,...?

                                                           

                                                          You can handle Cycle Group using GetCurrentField(GroupName) within the dollar sign expansion

                                                           

                                                          $(='[' & GetCurrentField([GroupName]) & ']') in place of PTY_VEND_GP_DES in the Aggr() expression above