Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
7 years ago
Solved

Count based on multiple criteria

I have this demotable. ColumnB has many different values, as well as Column C. Disregard ColumnA ColumnA Date ColumnB ColumnC Calculate count 07 21.9.2017 1000 A 1 07 22.9.2017 10...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hi Yggdrasill

     

    you can do thi sin 2 steps:

     

    first add this column:

     

     

    AddedCol1 = 
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] = EARLIER( Data[Date] ) - 1 
    )
) + 0

     

    and then this final column:

     

     

    AddedCol2 = 
IF(
    Data[AddedCol1] = 0,
    BLANK(),
    CALCULATE(
        SUM( Data[AddedCol1] ),
        ALLEXCEPT( Data, Data[ColumnC], Data[ColumnB] ),
        Data[Date] <= EARLIER( Data[Date] )
    )
)

     

     

     

Yggdrasill's avatar
Yggdrasill
Responsive Resident
7 years ago

Going back to this problem.

 

I have defined a holiday table which is merged to my date table. I want to exclude holidays, saturdays and sundays from my counter but doesn't the function EARLIER() expect consecutive dates ?

Example of the output (Correct Calc count)

ColumnADateColumnBColumnCCalculate count Correct Calc countDay of week 
721.9.20171000A1 15 
722.9.20171000A   6 
716.11.20171000A2 25 
717.11.20171000A   6Friday
NEW LINE20.11.20171000A3  2Monday
78.1.20181000A4 32 
79.1.20181000A   3 
712.1.20181000A5 46 
71.6.20181000A6 56 
73.10.20181000A7 64 
74.10.20181000A   5 
73.1.20191000A8 75 
711.2.20191000A9 82 
712.2.20191000A   3 

 

I tried to exclude the holidays and weekends from my calculations but I'm not getting the desired "correct" output 

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Yggdrasill 

     

    I think you just need to change the 'AddCol1' formula to:

     

    AddedCol1 = 
var subDays = IF( Data[Day of Week] = 2, 3, 1 )
return
ISEMPTY(
    CALCULATETABLE(
        Data,
        ALLEXCEPT( Data, Data[ColumnB], Data[ColumnC] ),
        Data[Date] =  EARLIER( Data[Date] ) - subDays
    )
) + 0
    • Yggdrasill's avatar
      Yggdrasill
      Responsive Resident
      7 years ago

      That could work well in case of mondays. However, I'm dealing with holidays too !

      I have a Holiday table merged to my datetable so all holiday rows are marked with 0 and 1 if their active. I basically need to count the days between the last active day and current active day and use that variable for the subDays

      • LivioLanzo's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        Hi Yggdrasill 

         

        are you able to post some sample data with your date table included?