0

I am trying to count number of ids which meet certain requirements:

  1. over 1k income for month a
  2. below 500 income for month a + 1

how do I achieve this in tableau? I tried using the following calculated filed: If {fixed [id],month[date]: sum(income)} >=1000 and {fixed [id],month[date]+1: sum(income)} <500 then 1 else 0 end

But not sure whether such month(date) + 1 method worked here.

peteraa
  • 11
  • 1

1 Answers1

0

Just create a new Date field, shifted forward by 1 month:

DATEADD('month',1,[Order Date])

For both dates, right-click Custom-Date --> Month-Year, doing so you can create both Actaul and Shifted date.

Then you only need a calculated field like this in order to count all months with that condition:

sum(if     { FIXED [Order Date (Month / Year)] : SUM([Sales])} >= 50000
       and { FIXED [Order Date + 1 Month (Month / Year)] : SUM([Sales])} <= 100000
      then 1
    end
)
Fabio Fantoni
  • 3,077
  • 3
  • 22
  • 32
  • Hi Fabio, thank you for your help. I added fixed [id] to your field, but the number seems to be larger than expected. Any idea why? – peteraa Aug 02 '21 at 10:36
  • please, try to add some sample input and output data, otherwise it's quite hard to understand the overall context – Fabio Fantoni Aug 02 '21 at 11:57