1 Reply Latest reply on Jul 18, 2018 1:49 PM by Patrick A Van Der Hyde

    Cumulatively sort multiple data source measures in table along one date field

    Rico de Feijter

      Hi I have the following issue.

       

      The following is an excerpt of the totals I have:

       

      As you can see Rekening 0210 yields a total of -36.160 which is made up of an amount (CURR_BALANCE) of -18.000 from one data source (query) and an amount of  -18.160 (holding) from the other datasource (2018 - 3+ Holding). Both amounts originate from another date. That is, -18.000 originates from 05-2013 and -18.160 originates from 03-2018.

       

      However, I want to plot these totals in a table along one Date axis in a cumulative manner. When I now try to do this and use the Jaar and Periode fields from the Query dataource I get the following result:

       

       

      As you can see only -18.000 is shown under 2013, which is correct. But, I also expect -36.160 (-18.0000 +- 18.160) in 03-2018. However, this amount is not shown in march 2018 (thus, amounts that originate from different data sources but are related to the same Rekening should be summed up):

       

       

      I thought that maybe because Jaar is from the Query datasource, Tableau does not take into account holding from the other data source. So I created another datasource, named Date (an Excel file), which stores the years 2013 and 2018 with their periods (1 up to including 12). I thought that maybe by blending the years and periods of Query and 2018 - 3+ (Holding) with the years and periods of Date, I would be one step further. However, when I use Jaar from Date I get the following result:

       

       

      Now -36.160 is shown under 2013, which is again not what I want.

       

      How can I solve this issue? Preferrably I would like to use year and period from Query as here all the periods and years I need are stored. Otherwise I need to maintain another Excel 'Dates' where all the years and periods with which both data sources (query and 2018 - 3+ (Holding)) are blended, are stored, which is not really convenient. Maybe important to note: I now took Rekening 0210 as  an example, but of course I want to apply the same principle to the remainder of the Rekeningen.

       

      Thanks in advance for your support.

       

      The workbook is attached.