Count status changes between today and last day

[NunoCampos]

I'm creating a pivot table that contains Reference Date, Trade Status and Name as Dimensions and as Measures I want to Display Count[Trade Status] - which is working as expected - and also a delta between today and last day on Reference Date - which I can't make it work.

I'm trying the following formula: =Count({<[Reference Date]={'$(=Date(Today()))'}>} [Trade Status]) - Count({<[Reference Date]={'$(=Date(Today() - 1))'}>} [Trade Status])

This is always returning 0.

I also tried to use: =Count([Trade Status])-above(Count([Trade Status]))

But in a pivot table the above function does not fulfill my needs and gets messed up.

Help is appreciated.

[dplr-rn]

as you have date field in the pivot table you have to use interrecord fucntions.

as its a pivot table you may need to use before and after rather than above below

https://community.qlik.com/t5/QlikView-Documents/Missing-Manual-Before-and-After/ta-p/1488064

[Vegar]

I notice that you have [Reference Date] as a dimension, but you are only calculating the difference between today and yesterday. It will probably be easier to get desired output if you skip using [Reference Date] as a dimension.

Can you confirm that you have output for today's date and yesterday's date? And that the format of [Reference Date] is the same format that you get from date (today())? 

[NunoCampos]

Thanks for the reply.
Before and after didn't suit my case since I'm not accessing values from existing columns but instead from previous dates.

[NunoCampos]

Thanks for the reply.
I was only calculation the difference between today and yesterday as an initial example. My idea is to be able to check for each [Reference Date] in comparison to the previous one.

I have output for both today's date and yesterday's date. Format seems equal, how can I make sure here is no difference on the format that I was unable to notice?

[MayilVahanan]

HI @NunoCampos 

If you want to display the Today - Yesterday reference date alone.

You can remove the Reference date as dimension and try with below formula.

=Count({<[Reference Date]={'$(=Date(Max([Reference Date])))'}>} [Trade Status]) - Count({<[Reference Date]={'$(=Date(Max([Reference Date])-1))'}>} [Trade Status])

If its not working, please give the sample data.

[NunoCampos]

That formula returns 0 on all value as well.
This is the data I have (picture attached):

Dimensions: [Reference Date] [Trade Status] [Name]
Measures: 

• =Count([Trade Status]) - Which gives me how many Names I have assigned with a Trade Status for each Reference Date
• =Count([Trade Status])-above(Count([Trade Status])) - Which gives me the change between Count([Trade Status]) from today and last day but doesn't work in the pivot table when I expand.
• Your formula - returning 0
  • What I want to display here is the new Names that were added/removed to each Trade Status. Something like the second formula (that only focus on the diff of Trade Status count) that can work in a pivot table