Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
EllaCymraeg
Contributor II
Contributor II

Aggr function help

Hello! We have this calculation that works:

if(Sum( {$<GLSubLedg.TransactionType = {"Payroll"}, GLSubLedg.ActualFlag = {'A'}, Currency = {"STAT"},[GLPeriod.PeriodMonthRP]=,[GLPeriod.PeriodNum]={"$(=only(GLPeriod.PeriodNum)-1)"},sum>} GLSubLedg.AmountNR)=0,

IF(Sum( {$<GLSubLedg.TransactionType = {"Payroll"}, GLSubLedg.ActualFlag = {'A'}, Currency = {"STAT"},[GLPeriod.PeriodMonthRP]=,[GLPeriod.PeriodNum]={"$(=only(GLPeriod.PeriodNum))"},GLSubLedg.PAY_Descriptor2={"*"}>} GLSubLedg.AmountNR)>0,

{<[GLPeriod.PeriodMonthRP]=,[GLPeriod.PeriodNum]={"$(=only(GLPeriod.PeriodNum))"}>} count(distinct([GLSubLedg.PAY_Descriptor2])),0),0)

 

but only when the column GLSubLedg.PAY_Descriptor2 is in the table. We would like to remove this column so that the data is summarised, but when we do that, the calculation returns zero. I think we need the AGGR function but everything I have tried has either returned zero, or returns the wrong answer. Can someone help with my syntax? 

Labels (1)
2 Replies
Chanty4u
MVP
MVP

Try this 

If(

  Sum(

    {$<

      GLSubLedg.TransactionType = {'Payroll'},

      GLSubLedg.ActualFlag = {'A'},

      Currency = {'STAT'},

      [GLPeriod.PeriodMonthRP]=,

      [GLPeriod.PeriodNum] = {"$(=Only(GLPeriod.PeriodNum)-1)"}

    >} 

    GLSubLedg.AmountNR

  ) = 0,

 

  If(

    Sum(

      {$<

        GLSubLedg.TransactionType = {'Payroll'},

        GLSubLedg.ActualFlag = {'A'},

        Currency = {'STAT'},

        [GLPeriod.PeriodMonthRP]=,

        [GLPeriod.PeriodNum] = {"$(=Only(GLPeriod.PeriodNum))"},

        GLSubLedg.PAY_Descriptor2 = {"*"}

      >}

      GLSubLedg.AmountNR

    ) > 0,

 

    Count(

      distinct 

      Aggr(

        Only({$<

          GLSubLedg.TransactionType = {'Payroll'},

          GLSubLedg.ActualFlag = {'A'},

          Currency = {'STAT'},

          [GLPeriod.PeriodMonthRP]=,

          [GLPeriod.PeriodNum] = {"$(=Only(GLPeriod.PeriodNum))"},

          GLSubLedg.PAY_Descriptor2 = {"*"}

        >} GLSubLedg.PAY_Descriptor2),

        GLSubLedg.PAY_Descripto

r2

      )

    ),

 

    0

  ),

 

  0

)

EllaCymraeg
Contributor II
Contributor II
Author

Thank you for this, but again, this only works when the column GLSubLedg.PAY_Descriptor2 is in the table. I have tried to add the AGGR function to the two IF statements, but that then returns zero.