3 Replies Latest reply: Aug 15, 2016 1:18 PM by Gysbert Wassenaar RSS

    how to calculate data in Qlik

    Vikas Goel

      I am a newbie using QlikCloud to build a simple set of dashboards for personal finance. I have all the raw data in an excel file (1 table per sheet). Until now i have simply built dashboards that use that data. i now want to build some charts that will display data that needs to be calculated using the data in my excel file. specifically, i have 2 tables:

      table 1 has columns: account, fund name, date, amount.

      table 2 has columns: fund name, asset class, asset_class_fraction_in_fund 

       

      i would like to calculate and plot a metric amount_in_asset_class by asset class, account and date.

      amount_in_asset_class(asset_class, account, date) =

      sum( fund name, amount(account, fund name, date) *asset_calls_fraction_in_fund(fund name, asset_class))

       

      what is the best way to implement this? i have no experience with scripting or writing formulas in QlikCloud. I could create a table in Excel to calculate this metric and read it from there, but I use a MAC and Excel doesnt have advanced pivot table features that would have enabled me to do this without using vlookup type granular functions

       

      Regards