1 Reply Latest reply on Jun 21, 2018 4:50 AM by Zhouyi Zhang

    IF formula using data from 2 data sources + chart problem

    Diana Lujza Toth

      Hi, could you pls help me. (You will see I am just learning the basics. )

       

      1) I got a huge database (LIST1) and a small excel spreadsheet (LIST2) connected by e-mail address:

       

      LIST1 headers and content (this is the main data source):

      account e-mail     |     order id     |     sales     |     units

      xxx@ gmail.com     123456               500               6

      yyy@ gmail.com     234567               350               4

      zzz@ exple.com     345678               200               1

      vvv@ exple.com     456789               600               2

      www@ anythg.com     56789              500               3

      ...

       

      LIST2 headers and content:

      account e-mail      |     rep-cust

      xxx@ gmail.com          REP

      www@ anythg.com     REP

      ...

       

      those who are in LIST1 but NOT in LIST2 are considered as "Pure Customers"

      those who are in both lists are considered as "Representatives"

      in the example: xxx@ and www@ are REPs, the rest is Pure Customer.

       

      I want to see the share of sales by Pure custs and Reps. I know it is very simple but am not able to write the correct IF formula to see the results. Reps are OK, but Pure Customers are marked with "Null", but it looks dummy in the chart.

      What I simply wrote was:

      IF [rep-cust]=NULL THEN "Pure Customer" ELSE "Representative"

      END

       

      I also tried

      IF [rep-cust]="REP" THEN "Representative" ELSE "Pure Customer"

      END

      I want my chart to show "Representative" and "Pure Customer".

       

      2) my chart problem: I have campaign numbers in a database like: 201701, 201702, .... , 201712, 201801, 201802, 2018... (stored as numbers), and although they have 12 campaigns/year, these are not equal to the real months of the year (e.g. campaign 201805 starts on 27 April and ends on 30 May). I would like to make a line chart, but if I put campaign number to columns, the chart does not appear because of the number format. I would also like to compare data to previous year in the line chart. Do I have to define campaign in a calculated field, is this the only way to show data by campaign in chart?

      thank you.