2 Replies Latest reply on Sep 6, 2019 6:19 AM by Toby Erkson

    I have requirement that calculating effect on sale

    prashant patel

      @Tableau version 2019.2.2(20192.19.0)64 bit@




      ..The main requirement is that we have to find out the effect of that campaign on sale ...

      means before campaign and after 22 days  campaign sale .


      i have campaign start date but dont have campaign end date ..i discussed with teams but not having that field...so we are assuming the scenario  in our database 1 field is there email send date  so which email is send at last we assume that is last day of that particular campaign communication..

      though i need sale before each campaign and after each campaign..


      for after campaign we assume that max 22 days of transaction period is campaign effectiveness and those customer who make transaction in that 22 days after campaign over then we say here some effect will be done .



      campaign 1 start at======= 1 jan 2019 to 10 jan 2019 as new year sale ...

      campaign 2 start at ====15 jan 2019 to last date of email send date of that perticular campaign ..assume 15 jan 2019 to 25 jan 2019 as Republic Sale...


      we have to find out that how many customer bought or make transactions after 20 jan 2019 to 12 feb 2019(22 days) for campaign 1 ...

      and 15 jan 2019 to 25 jan 2019 as Republic Sale...?


      and how many customers come to store and how much sale is done in between (20 jan to 12 feb 2019 in 22 days after campaign1)

      likewise other campaign also ..



      how much sale before that campaign and after that campaign...


      but the cavet is that in 1 month there could be 2-3-4 campaigns are running so .....but for time being sales before capaign and after campaign

      and number of customers come in the period of that 22 days ...



      following is the example which i refer for calculation...is this ok?


      1.can we make parameter for campaign ===campaign1,campaign2,campaign3...campaign145?


      2.can i use {fixed [campaign id] : if [trx dt] <=  [campaign start date] then sale else 0]


      { FIXED [Trx date]:MAX(

      IF [Trx date]>=[campaign start date]

      AND [Trx date]<=[last email send date of that perticular campaign ]

      THEN "campaign id"


      but it shows aggregated values not allowed



      1. Create parameter [Report Date]

      2. Calculate the date each customer purchased the catalog, [MMPCAT BILL_DT]

      { FIXED [campaign id]: MAX(IF [PROD_ID] = "MMPCAT" THEN [BILL_DT] END)}

      3. Calculate sales before catalog purchase, [SALES BEFORE MMPCAT]

      { FIXED [BSNSS_ID]: SUM(IF [BILL_DT] >= DATEADD('year', -1, [MMPCAT BILL_DT]) AND [BILL_DT] <= DATEADD('year', -1, [Report Date]) THEN [NET_AMT] END)}

      4. Calculate sales after catalog purchase, [SALES AFTER MMPCAT]



      Hope this helps.




      4. LOGIC@@

      Here´s the solution example, first i LEFT JOINED the sales data on the campaign data, then i wrote a calculated field with


      IF [DEPT CODE]=[Category (Sales)] AND


      DATEDIFF('day',[Sent Date],[Purchase Date])>0 AND


      DATEDIFF('day',[Sent Date],[Purchase Date])<30 THEN [Sales Total] END


      The Result is


      Campaign 1: 500
      Campaign 2: 100

      Campaign 3: 100


      how can i put logic..????i am really getting brained blast.



      is email send= total email send

      email sent date,---for each customer when email send

      campaign start date==for particular campaign when campaign is started

      trx date.==when customer make any transaction

      partyuid==unique customers

      campaign id/promo id== particular events or activities for sale or for social cause for information

      member created date=(created)....when that particular customer profile is made


      please give me flow and logic calculation which i CAN use  for this requirement....i stuck in all the side


      Newbie, Tableau DesktopForumsGetting Started in the ForumsOkechukwu Ossaiswaroop.gantelaDeveloper Forums


      Message was edited by: prashant patel

        • 1. Re: I have requirement to create Email Marketing the dashboard ..i am new to tableau so dont have enough knowledge on calculations ...so please help me out
          Tim Beard

          Hi prashant patel

          It would help if you include the workbook with the dashboards, not just the data. However, I can already see that you seem to be working with an extract that does not include the raw data for the campaign. Either that or data is not collected with enough detail as the email opening count has already been summed on a per customer basis as far as I can see.


          Once you have the appropriate data, I would guess that a COUNTD (count distinct) function would do the job for you. It could be that you need to use a Fixed LOD calculation (there are plenty of resources online for these), but my guess is that a simple COUNTD would be sufficient.


          As a quick and dirty approach to use with the current data set you could possibly use a calculated field that generates a 1 for customers that have opened a mail and a zero for those that have not: IF ([Email Open Count]) =0 THEN 0 ELSE 1 END. You could then sum this to get the number of customers that have opened the mail


          A couple of tips for your journey into the world of Tableau:

          • Take the time to go through the free training videos on the Tableau web site and use the demo workbooks provided with each. It's worth the time. They're very good and I still refer back to them every now and again when I am getting myself confused about something.
          • Understand your data. See my post and the attached links here. Going back to the basics of what is in your data source can be extremely helpful when something does not seem to be behaving the way you expect or want. Key word: grain!


          • 2. Re: I have requirement that calculating effect on sale
            Toby Erkson


            Please read this document on the proper use of @Mentions:  @Mention Etiquette Guide