Count/distinct over time

[#1]

I am working on counting how many times the promise date for an order changed over time. The pivot table I made looks like this:\\\

Order	3/2/2015	3/3/2015	3/4/2015
2312323	5/3/2015	5/6/2015	5/10/2015
1234324	6/3/2015	6/3/2015	6/3/2015
5678901	4/16/2015	4/19/2015	4/16/2015

The dates in the data cells are the promise dates. My question is if there's a way of counting how many times each order's promise dates changed during the life of the order? Currently I am using Count(Distinct()) but in a case like Order #5678901, it would only count that it changed once because it went back to the same promise date.

This is my current expression in the table where I am counting: if((count(distinct([Promise Date]))-1)>=0,(count(distinct([Promise Date]))-1),'-')

The results I would want in this example would be:

Order, # of Changes

2312323, 2

1234324,0

5678901,2

I'm not sure if this is possible but I would appreciate any help I can get.

[#2]

I believe I put it in correctly but it came up with 0... I put it in the pivot table with the Date and Order as dimensions and I put it in a straight table with the Order as a dimension and it came up with 0 both times.

I tried both ways in the script too and each came up with 0 for all the values for each Order...

[Qrishna]

Could you please attach the sample qvw?

[Qrishna]

reload the attached file and let me know.

Thanks

krishna

[#5]

I think it worked! What did you change? I'll keep you posted as a use it further/add more data, but thank you so much!

[Qrishna]

Awesome.Please mark this post as 'Answered'.

Krishna

[#7]

Hi Krishna,

So this works for the most part, but really only in the times when it says there are 3 promise date changes is when it gets messed up. When I just select the ones with 3 changes, and then go to the "do not open w/o filter" tab, you can see across that 3 is not the correct number for a few of those. I have really only found issues with a few of those ones. The ones with 2 promise dates don't seem to have issues, but I'm not sure.I am attaching another sample file to this post.I have tried to change Peek to Previous and it hasn't change anything.

[Qrishna]

Hi Alex,

I am having a hard time in opening the App and the app is too slow(figure out the expressions once which are responsible for this).

Could you please remove all the sheets except the one which we are discussing and send me the qvw again.?

Thanks

[Qrishna]

Alex,

As you can see the below screenshot, the expression is giving expected correct result.

check the script or the business rule where you might have forgot to apply for the  on the promise date in the script.

note: even though i try to change the script, i cannot reload and check for the correctness unless i have the source data.

In the above screenshot, there are 3 date changes as expected.

thanks

krishna

[#10]

What I was looking for is would be 1 change because the promise date changed from 3/9/2015 to 3/27/2015, so that would be one change. I'm not sure if it is possible to manipulate it to do that.  I would try putting -1 at the end of the sum but then it would affect the ones that are already correct. However the script that you gave previously does work for when I am trying to track how many times the status changed! I am going to keep looking at the Promise Dates part though.

[Qrishna]

Check for the IDs which has date changes# 3.

As in the above picture, i see there are 3 date changes but m not sure what exactly is happening within data model as i cannot reload unless i have source data.