Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

donatodonato
New Contributor

Count data of yesterday

Hi all,

I want to count the number of the activations that happened only yesterday. In particular i want to count all the activation that have happened on a particular channel (VP) and I want to see only the activations happened yesterday.

My fields are:

ACTIVATION_CHANNEL for the channel

ACTIVATION_DATE for the date).

ACTIVATION is what I want to count

I used the set analysis and i managed to see the activations that have happened from yesterday until the date I'm checking the report (if I check the report today at 6pm I can see all the activations happened yesterday and today untile 6 pm

This set analysis is working

count(distinct{$<ACTIVATION_CHANNEL={'VP'},ACTIVATION_DATE = {">$(=Date(Today()-2,'YYYYMMDD'))"}>}ACTIVATION)

If I use the following set analysis I can see the activations happened today

count(distinct{$<ACTIVATION_CHANNEL={'VP'},ACTIVATION_DATE = {">$(=Date(Today()-1,'YYYYMMDD'))"}>}ACTIVATION)

However, I'm not able to see the activation happened only yesterday.

I tried the following set analyses but they are not working:

count(distinct{$<ACTIVATION_CHANNEL={'VP'},ACTIVATION_DATE = {"$(=Date(Today()-2,'YYYYMMDD'))"}>}ACTIVATION)

this one is the same as the first set analysis but without the >


count(distinct{$<ACTIVATION_CHANNEL={'VP'},ACTIVATION_DATE = {">$(=Date(Today()-2,'YYYYMMDD'))<$(=Date(Today()-1,'YYYYMMDD'))"}>}ACTIVATION)


count(distinct{$<ACTIVATION_CHANNEL={'VP'},ACTIVATION_DATE = {">$(=Date(Today()-2,'YYYYMMDD'))"},ACTIVATION_DATE = {"<$(=Date(Today()-1,'YYYYMMDD'))"}>}ACTIVATION)



Here, an example of the format of the ACTIVATION_DATE field


 

ACTIVATION_DATE
20170908
20170920
20170904
20170905
20170907
20170906
20180222
20171108
20171120
20171116
20171114



Do you have any suggestion how to show only the activation of yesterday?


Thank you in advance



Tags (3)
2 Replies
MVP
MVP

Re: Count data of yesterday

Use single quotes instead of double quotes. Then you will have to escape the single quotes enclosing YYYYMMDD by having them twice:


count(distinct{$<ACTIVATION_CHANNEL={'VP'},ACTIVATION_DATE = {'$(=Date(Today()-2,''YYYYMMDD''))'}>}ACTIVATION)


NB - it is not double quotes around YYYYMMDD but two consecutive single quotes on each side.


donatodonato
New Contributor

Re: Count data of yesterday

I tried but it doesn't work.

I cannot see any result with this set analysis

Thank you

Community Browser