Count IF for Table of Model

[superDaddy]

Hi, 

I have a very basic model (still learning)

F_METADATA contains a list of articles published on my site:

| %Page_Path_Key | Published Date |     Title    |
|----------------|----------------|--------------|
| content/A      | 1/5/2021       | Example A    |
| content/B      | 1/12/2021      | Example B    |
| content/C      | 1/14/2021      | Example C    |
| ...            | ...            | ...          |
| content/XYZ    | 8/29/2021      | Example XYZ  |

I am trying to calculate how many published articles there were in a particular DAY_DATE. For instance, given the previous examples:

• 1/5/2021 has 1 published article
• 1/6/2021 has 1 published article
• ...
• 1/12/2021 has 2 published articles
• ...
• 1/14/2021 has 3 published articles
• 1/15/2021 has 3 published articles
• ...
• 8/30/2021 has 411 published articles

I am using the following formula:

count(distinct if(Published_Date <= DAY_DATE , Title))

and it is not delivering what I was expecting...  The result for 8/30/2021 is 387. This may be normal, as for sure it is checking on the model data and possibly there are articles that, although published, haven't got any traffic.

How can I apply this formula to F_METADATA instead, so I can figure how many articles were published on that day?

I have shared the following Google Sheet, which I hope it helps to understand my task: https://docs.google.com/spreadsheets/d/1wmOOf0B190NrHBzbY-Sd9uQNR4-5FujizLdLm4UoFKA/edit#gid=0

[JordyWegman]

Hi,

I'm not sure, but is the number of items related to the number 'content/411'? Because why is it 411 on the 30th of August (doesn't seem like a sum). But the the 12th of January seems like a sum (1 + 2).

I need to know this before I can give the solution.

Jordy

Climber

[superDaddy]

Hi,

@JordyWegman sorry for the misunderstanding. I simplified the question (removing unnecessary info and changing data) and added a full example in Google Sheet. There is no sum, just a distinct count for the given day. What you initially saw was just a coincidence. F_METADATA has more observations than those displayed there.

For every single day on the autocalendar table, Qlik needs to evaluate how many observations, in the F_METADATA table, had a published date that was smaller or equal to the evaluated day. This way, we calculate how many published articles there were on that particular day.

Maybe we need to do that on the Data Model Script  itself and not as a calculated metric/dimension?

[JordyWegman]

Hi,

Maybe you can use set analysis for this:

count(distinct {$< Published_Date = {"<=DAY_DATE"} >} Title )

Jordy

Climber