topic Re: Count status changes between today and last day in App Development

Count status changes between today and last day

NunoCampos — Sat, 16 Nov 2024 01:11:12 GMT

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.

Re: Count status changes between today and last day

dplr-rn — Mon, 02 Nov 2020 17:07:57 GMT

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

Re: Count status changes between today and last day

Vegar — Mon, 02 Nov 2020 17:15:26 GMT

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())?

Re: Count status changes between today and last day

NunoCampos — Tue, 03 Nov 2020 08:29:33 GMT

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.

Re: Count status changes between today and last day

NunoCampos — Tue, 03 Nov 2020 08:31:46 GMT

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?

Re: Count status changes between today and last day

MayilVahanan — Tue, 03 Nov 2020 08:38:10 GMT

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.

Re: Count status changes between today and last day

NunoCampos — Tue, 03 Nov 2020 12:17:03 GMT

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