1 Reply Latest reply on Oct 5, 2018 7:49 AM by venkatram

    Partition by and Sum in Calculation

    Talha Iftikhar

      Hi Tableau Users,

      Please spare some time if you have a solution for this.

       

      The requirement I have is to create a Daily Report (we will be selecting a Day in a filter).

       

      I would need to create one column as an Average of last ten instances of each [keyword id], Like if we select 21Sep from our Filter So for each keyword present on that day, I would need to calculate Avg of last ten Instances of that [Keyword id] order by Date desc before the selected Day.so referring to Image below, for each keyword tableau should go before 21sep  and pick ten instances of that [Keyword id] and calculate Avg of Cost for that ten days,  For Ref:

      avgNeeded.png

       

      We will be applying a filter so we need to go for a LOD here so we can calculate on back days, I tried to partition and rank based on keywordid,Day and sum but No avail. Here is the SQL query ran to achieve this:

       

       

      Query: select account,day,keywordid,cost/clicks AS CPC,row_number() over (partition by keywordid order by day desc)  from [PPC.Keywords] where keywordid='10405032' order by day desc

       

      Note: I need as a calculation so I can perform some more formulas.so Index() with edited table calculation can`t work I guess.

       

      Your Help will be appreciated. Thanks