Change in Month data

[#1]

I have the following formula that is not working.  I am looking in the +/- change Month over Month.  I am only getting the same value as the current month with the below formula.

=Num(Count(Distinct{<[Monthly Issue Severity]={High},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID])-Count(Distinct{<[Monthly Issue Severity]={High},[Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'}>} [Monthly Issue ID]),'0') 

Any assistance would be appreciated!

[vishsaggi]

Put single quotes in

{<[Monthly Issue Severity]={'High'}

and Try.

[#3]

Thank you, I actually did have those in the calculation, but they didn't transfer over.

=Num(Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID])-Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'}>} [Monthly Issue ID]),'0') 

I still have the same amount month over month, and not the Change, I am looking for.

[vishsaggi]

What do you get if you just use one single count expression in a text object? Did you try?

Just single expression i mean..

Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID])

Can you share a sample app you are working on?

[#5]

I have that in a different column.  That is how I know that the numbers current month and change are the exact same.

For the H column the calculation is:

=Num(count(Distinct{<[Monthly Issue Severity] ={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID]),'##,###')

For the +/- (MoM) column, the calculation is :

=Num(Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]))'}>} [Monthly Issue ID])-Count(Distinct{<[Monthly Issue Severity]={'High'},[Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'}>} [Monthly Issue ID]),'0') 

As you can see, the results are the exact same, and they should not be.  I am looking for the change month over month in the right column.

[vishsaggi]

Seems correct to me, but cant test unless i have some sample data. Can you share some sample data please??

[#7]

Attached is the data that is being used by my Pivot table.

[vishsaggi]

Your excel sheet has only two dates, 1/1/2017 and 12/1/2016, when you do Max(Load Date) - 1 what is the date you are expecting?

[uacg0009]

Hi Silvia,

I think you need to test the date type in the set analysis, because in the set analysis the data type should be the same.

In your example, [Monthly Load Date] = {'$(=Max([Monthly Load Date]) - 1)'} :

I think maybe the [Monthly Load Date] is the date type, but the Max([Monthly Load Date]) - 1, i think it is a number type, you should use date() function to change the it, maybe like date(Max([Monthly Load Date]) - 1), you can try it.

Thanks.

[#10]

Hi Vishwarath

the Max Load Date - 1 should return with the data from 12/1/2016.  so the change in should be the +/- between 1/1/2017 and 12/1/2016.