10 Replies Latest reply: Nov 7, 2016 7:31 AM by Ganesh Bagavathi RSS

    Including Dimensions in Set expression

    Ganesh Bagavathi

      Hello,

       

      I am looking for Set expression which will consider the last column to calculate the Nr column. In the example below the value

      Aus-Oct2016 should be 8. It is being shown as 14 here due to the entries in one of the Fact tables. the SITE_DT column is from a different Fact table and i want the Nr to calculate the days from the Site_DT.

      Please let me know if more information is needed.

       

      Nr:

      Max({<DT_CONS_DIM.FISC_YR_NBR={"$(=Only({1}YearofToday))"},

      DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"}>}DT_CONS_DIM.FISC_DAY_OF_MTH_NBR)

       

      PeriodCTRY_NAMDrNrDate(Max(INV_RPT_TRNS.SITE_DT))
      Oct-16Australia281410/9/2016
      Oct-16Canada28810/9/2016
      Oct-16Germany281510/16/2016
      Oct-16IN281510/16/2016
      Oct-16India2814
      Oct-16Mexico281510/16/2016
      Oct-16Netherlands28810/9/2016
      Oct-16UN28810/9/2016
      Oct-16US281510/16/2016
      Oct-1628
      Sep-16Australia35359/25/2016
      Sep-16Canada35299/25/2016
      Sep-16Germany35349/25/2016
      Sep-16IN35299/25/2016
      Sep-16India3535
      Sep-16Mexico35349/25/2016
      Sep-16Netherlands35299/25/2016
      Sep-16UN35299/25/2016
      Sep-16US35359/25/2016
      Sep-1635
      Aug-16Canada28228/21/2016
      Aug-16Germany28228/21/2016
      Aug-16IN28228/21/2016
      Aug-16Mexico28228/21/2016
      Aug-16UN28228/21/2016
      Aug-16US28288/21/2016
      Aug-1628

       

      Thanks.

        • Re: Including Dimensions in Set expression
          Giuseppe Gallina

          Hello,

           

          I'm not sure if I have understand. Please can you post a test .qlw file?

          Thank you.

            • Re: Including Dimensions in Set expression
              Ganesh Bagavathi

              Hello,

               

              I will not be able to post a sample file here, however i have solved hafl the problem, by including the P(INV_RPT_TRNS.SITE_DT) in my Set expression.

               

               

              Max({<DT_CONS_DIM.FISC_YR_NBR={"$(=Only({1}YearofToday))"},

              DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"},INV_RPT_TRNS.SITE_DT=p(INV_RPT_TRNS.SITE_DT)>}DT_CONS_DIM.FISC_DAY_OF_MTH_NBR)


              Now the remaining half of my problem is to calculate the Sum of one of the measures using the below expression, but it is returning zero.


              =Sum({<FISC_YR_NBR={"$(=Only({1}YearofToday))"},INV_RPT_TRNS.SITE_DT=p(INV_RPT_TRNS.SITE_DT),DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"}>}SLS_RPT_TRNS.COST_USD_AMT )

            • Re: Including Dimensions in Set expression
              Ruben Marin

              Hi Ganesh, I don't understand the required operation either.

               

              For your comments, at least for Oct-2016, seems you want "Day(Date(Max(INV_RPT_TRNS.SITE_DT)))-1", but I don't know the operations for other Periods (or the '14' for India in Oct-2016).

               

              In any case note that the $-expansion is calculated for the whole table, not row by row, so "$(=Num(Max(INV_RPT_TRNS.SITE_DT)))" will return the Max of all the selected data (same value for all rows).

                • Re: Including Dimensions in Set expression
                  Ganesh Bagavathi

                  Hello Ruben,

                   

                  It is not simple as "Day(Date(Max(INV_RPT_TRNS.SITE_DT)))-1" , it is actually showing the days from a different fact table where there entries, but i want to restrict them to the SITE_DT mentioned here.

                  And this SITE_DT is from another Fact table.

                   

                  In simple terms, I want to Sum a particular measure from the first Fact table till the date from the second fact table. But when i use the Max() as you have mentioned, it is not doing it by rows. Any suggestions to overcome this?

                   

                  Thanks.

                • Re: Including Dimensions in Set expression
                  Ganesh Bagavathi

                  Hello,

                   

                  I used the below expression to get the right numbers, now the challenge is to calculate the Sum of a particular measure using this function in Set expression.

                   

                  This works as expected:

                  FirstSortedValue(FISC_DAY_OF_MTH_NBR,-Aggr(FISC_DAY_OF_MTH_NBR, INV_RPT_TRNS.SITE_DT,CTRY_NAM))

                   

                  This does not work as expected, showing only zeroes:

                  =Sum({<FISC_DAY_OF_MTH_NBR={"<=$(=FirstSortedValue(FISC_DAY_OF_MTH_NBR,-Aggr(FISC_DAY_OF_MTH_NBR, INV_RPT_TRNS.SITE_DT,CTRY_NAM)))"}>}SLS_RPT_TRNS.COST_USD_AMT)

                   

                        

                  Oct-16Australia287010/8/2016
                  Oct-16Austria2814010/15/2016
                  Oct-16Canada2814010/15/2016
                  Oct-16China2814010/15/2016
                  Oct-16Germany2814010/15/2016
                  Oct-16Hong Kong2814010/15/2016
                  Oct-16IN2814010/15/2016
                  Oct-16India28 0
                  Oct-16Mexico2814010/15/2016
                  Oct-16Netherlands287010/8/2016
                  Oct-16Switzerland2814010/15/2016
                  Oct-16Thailand2814010/15/2016
                  Oct-16UN2814010/15/2016
                  Oct-16US2814010/15/2016
                  Oct-16 28 0
                  Sep-16Australia3535010/1/2016
                  Sep-16Austria3535010/1/2016
                  Sep-16Canada3535010/1/2016
                  Sep-16China3535010/1/2016
                  Sep-16Germany3535010/1/2016
                  Sep-16Hong Kong3535010/1/2016
                  Sep-16IN3535010/1/2016
                  Sep-16India35 0
                  Sep-16Mexico3535010/1/2016
                  Sep-16Netherlands3535010/1/2016
                  Sep-16Switzerland3535010/1/2016
                  Sep-16Thailand3535010/1/2016
                  Sep-16UN3535010/1/2016
                  Sep-16US3535010/1/2016
                  Sep-16 35 0
                  Aug-16Australia282808/27/2016
                  Aug-16Austria282808/27/2016
                  Aug-16Canada281408/13/2016
                  Aug-16China282808/27/2016
                  Aug-16Germany282808/27/2016
                  Aug-16Hong Kong282808/27/2016
                  Aug-16IN282808/27/2016
                  Aug-16Mexico282808/27/2016
                  Aug-16Netherlands282808/27/2016
                  Aug-16Switzerland282808/27/2016
                  Aug-16Thailand282808/27/2016
                  Aug-16UN281408/13/2016
                  Aug-16US282808/27/2016
                  Aug-16 28 0
                  • Re: Including Dimensions in Set expression
                    Ganesh Bagavathi

                    Attaching the sample application here, please give me your suggestions.

                    Intention is to calculate the Fourth column based on the dates in the last column or the Nr column which is the day number of that month.

                      • Re: Including Dimensions in Set expression
                        Jayant Tibhe

                        I dont know whats the problem in set expression and not exactly getting what you want to do.  But even if you

                        put simply  =Sum(SLS_RPT_TRNS.COST_USD_AMT)  

                        it shows all Zeros... that means there are no Non zero Measures against the Dimensions in the Data model.

                        • Re: Including Dimensions in Set expression
                          Ruben Marin

                          Hi Ganesh, as Jayant saids, there is nothing to sum, not only with the current dimension but in all the data. If you check the [document properties]->Tables you can see there is no records for the SLS_RPT_TRNS table.

                           

                          Maybe it's because the where condition, it's filtering to retrieve only data for this month and i'm not sure if that comparison is working, the date format of the document it's different that the one used in the where condition.

                           

                          Date format of the document: M/D/YYYY

                          Date format used in condition: YYYY-MM-DD

                           

                          To avoid issues with dates format usually it's better use the numeric value that represent the date.

                            • Re: Including Dimensions in Set expression
                              Ganesh Bagavathi

                              Thanks for your reply Ruben.My bad, let me upload the one with data. But I am using the number format for dates,

                               

                              =Sum({<DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"}>}SLS_RPT_TRNS.COST_USD_AMT)


                              My requirement is to restrict the Sum(SLS_RPT_TRNS.COST_USD_AMT) to the dates SITE_DT but it is being shown for the entire period now.


                              For Australia, the value should be 12533601 which is the value from the beginning of Oct2016 till 8th oct, but it being shown as 307899808 which is the value for the entire month of Oct.



                          • Re: Including Dimensions in Set expression
                            Ganesh Bagavathi

                            Can someone help me out here please?

                            Thanks