1 Reply Latest reply: May 12, 2017 11:52 AM by Sunny Talwar RSS

    Aggregation Currencies for all Dimensions

    Jan-Marten Daling

      Some Aggregation help please

       

      I am busy including currencies in my model and I want to create a master item that has the converted value which users can include in their visualisations.

       

      Here I have all the transaction amounts (where the transaction type is Payment) multiplied by currency factors. Note that the visualisation also includes the Branch dimension:

      sum(aggr(sum({$<[Transaction Type] = {"Payment"}>}[Transaction Amount] * [_CurrencyFactor]), [_CurrencyFactor], [Branch]))

       

      But I want a master item which is dimension independent. If I remove Branch like this:

      Collected Converted:
      sum(aggr(sum({$<[Transaction Type] = {"Payment"}>}[Transaction Amount] * [_CurrencyFactor]), [_CurrencyFactor]))

       

      When I add these fields to a table the latter does not resolve except for some :

      QlikTable.jpg

       

      The model:

      QlikModel.jpg

       

      I am sure that I am missing something. Surely there should be a way.
      Thanks for your consideration

      Marten Daling

        • Re: Aggregation Currencies for all Dimensions
          Sunny Talwar

          Isn't this expression dimension independent for you?

          Sum(Aggr(Sum({$<[Transaction Type] = {"Payment"}>}[Transaction Amount] * [_CurrencyFactor]), [_CurrencyFactor], [Branch]))

           

          as long as you Branch is the most granular level you will look at. But if you want to this at individual row level, why not do just this

          Sum({$<[Transaction Type] = {"Payment"}>}[Transaction Amount] * [_CurrencyFactor])