Forum Discussion

Hariharan_R's avatar
Hariharan_R
Solution Sage
7 years ago

Filter and Dynamic Result

Hi      I have below tables and i need to get "Default/Bonus" (red colour columns) column on Trans table. Also mentioned the logic for the columns.     We will be using RLS to filter the student....
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

Create this calculated column formula in the Trans Table

=IF(CALCULATE(COUNTROWS(Score),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime])))>0,CALCULATE(SUM(Score[Bonus]),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime]))),CALCULATE(SUM(Score[Default]),FILTER(Score,Score[StartDate]<=EARLIER(Trans[CourseDateTime])&&Score[EndDate]>=EARLIER(Trans[CourseDateTime]))))

Hope this helps.

  • Hariharan_R's avatar
    Hariharan_R
    Solution Sage
    7 years ago

    It is not giving the expected result. 

     

    Score table's year and Trans table's year should be considered and the value is based on the student logs in. if student B logsin then subject S2 only considered on the Trans table new columns.

     

    Your script showing below result.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      Share data from both tables such that i can paste those in an Excel file.  Also, show the exact result you are expecting.

      • Hariharan_R's avatar
        Hariharan_R
        Solution Sage
        7 years ago

        Hi

         

        Table: Subject

        Subject
        StudentSubject
        AS1
        BS2
        CS1

         

        Score
        StartDateEndDateYearDefaultBonusSubject
        3/1/2019 1:003/3/2019 4:00201956S1
        2/1/2018 1:004/4/2018 4:0020181110S1
        4/1/2019 1:004/3/2019 4:00201942S2
        2/1/2018 1:003/6/2018 4:0020181213S2

         

        IDCourseDateTime
        13/1/2019 2:00
        23/1/2019 3:00
        13/3/2019 3:00
        23/4/2019 3:00
        33/4/2019 0:00
        43/4/2018 0:00
        51/1/2018 1:00


        If Student         A logs in the below result.
        Expected result is as like below.

        IDCourseDateTimeDefault/BonusCourseDateTime + (Default/Bonus)
        13/1/2019 2:0063/1/2019 8:00
        23/1/2019 3:0063/1/2019 9:00
        13/3/2019 3:0063/1/2019 9:00
        23/4/2019 3:0053/4/2019 9:00
        33/4/2019 0:0053/4/2019 5:00
        43/4/2018 0:00103/4/2018 10:00
        51/1/2018 1:00111/1/2018 0:00

         

         

        If Student B logs in

        IDCourseDateTimeDefault/BonusCourseDateTime + (Default/Bonus)
        13/1/2019 2:0043/1/2019 6:00
        23/1/2019 3:0043/1/2019 7:00
        13/3/2019 3:0043/1/2019 7:00
        23/4/2019 3:0043/4/2019 7:00
        33/4/2019 0:0043/4/2019 4:00
        43/4/2018 0:00133/4/2018 13:00
        51/1/2018 1:00121/1/2018 13:00

         

        The logic for the Default /Bonus column should be

         

        if(CourseDateTime>Score.StartDate
        && CourseDateTime<Score.EndDate, Bonus, Default)