Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
NunoCampos
Contributor
Contributor

Count status changes between today and last day

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.

 

Labels (3)
6 Replies
dilipranjith
Partner
Partner

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
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
NunoCampos
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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.

Thanks & Regards,
Mayil Vahanan R
NunoCampos
Contributor
Contributor
Author

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

NunoCampos_0-1604405268716.png