11 Replies Latest reply on Oct 24, 2016 9:47 PM by venkata sreekanth

# How to find out matched Cases between Current month & Previous Month

Dear Team,

I have attached Sample data & sample workbook in attachments.My expected output is I want to calculate the Matched  count of CustomerID based on my selected month & Previous month

Thanks in Advance !!!!

Warm regards,

Venkata Sreekanth

• ###### 1. Re: How to find out matched Cases between Current month & Previous Month

Hi Sreekanth,

Is this something similar what you are looking for

Attached sample workbook for your reference

Thanks

• ###### 2. Re: How to find out matched Cases between Current month & Previous Month

Hi Praveen,

Thanks for your quick help!!!

The requirement is like We have to calculate the Month wise CountD(CustomerID) based on previous month.

Please find the tableau below will give u the clarity on my expected output

Month
CountD CustomerIDMatched CustomerID with Previous  Month
Apr16100 (Because we dont have Mar 16 data in our excel)
May16147(Approximately)
Jun16105(Approximately)

Thanks in Advance !!!!

Warm regards,

Venkata Sreekanth

• ###### 3. Re: How to find out matched Cases between Current month & Previous Month

Hello Venkata,

Try this calculated field :-

It's probably not the most "Tableau" type answer, but (I think) it is mathematically correct.

Sean

• ###### 4. Re: How to find out matched Cases between Current month & Previous Month

Hi Sean,

I have tried your formula, but it throwing wring result.For more clarification i have already attached sample workbook in my first post.

Please have a look on it.Try to implement your logic in that book & share here.

Thanks in Advance !!!!

Warm regards,

Venkata Sreekanth

• ###### 5. Re: How to find out matched Cases between Current month & Previous Month

Hello Venkata,

The workbook is attached (I'm using Tableau version 9.3)

in case that does not work here is the text of the calculated field

sum(

{fixed [Customer ID] :

zn(

avg(if DATEDIFF('month',[Parameter 1],[Month])=0 then .5 end ) +

avg(if DATEDIFF('month',[Parameter 1],[Month])=-1 then .5 end )

)

}

)

Good luck.

S.

• ###### 6. Re: How to find out matched Cases between Current month & Previous Month

Hi Sean,

Thanks for quick help.Right now we can able to show the matched values for selected month.But i want to show,matched count in a bar chart.Is it  possible ?

Warm regards,

Venkata Sreekanth

• ###### 7. Re: How to find out matched Cases between Current month & Previous Month

Hi Venkata,

I have created 2 calculated fields for solve this problem.

One is with lookup on month "Lookup Customer" -

if attr([Customer ID])==lookup(attr([Customer ID]),-1) then 1 else 0 end

This field is computed on Month.

Another field is count of customer -

if first()==0 then window_sum([Lookup Customer]) end

This field is computed on Customer ID.

I have put the Customer ID into detail section. Also, don't forget to put this field into filter to remove the nulls.

You can also solve this problem using LOD.

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 8. Re: How to find out matched Cases between Current month & Previous Month

Hello Venkata,

I had another look at your question and came up with this

I'm still not entire sure why this works, it's something to do with (I think) counting the presence of data regardless to it's actual value.

Also the Totals are done using the Analytics "Average Line" and then changing it to a total.

I've tried to attach the workbook to this post  but the Attach link seems to have disappeared from the editor.

S.

• ###### 9. Re: How to find out matched Cases between Current month & Previous Month

Hi Sean,

Thanks & Regards,

Venkata Sreekanth

• ###### 10. Re: How to find out matched Cases between Current month & Previous Month

The workbook is attached.

S.

• ###### 11. Re: How to find out matched Cases between Current month & Previous Month

Hi Sean,

Thanks for your efforts and really appreciated.

After archiving this result the requirement get more complicated. Now the requirement is changed to below table format.

Month

CountD(CustomerID)

X1

Matched IDs with Previous Month

X2

% Calculation (X2-X1)/X1
Apr16100
May16147(7-14)/7= %
Jun16106-66.6%

I have to calculate "% of calculation" .

Expected output in bar-chart.We have to Show month wise % Calculation in bar-chart.