Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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
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?
Hi,
Maybe you can use set analysis for this:
count(distinct {$< Published_Date = {"<=DAY_DATE"} >} Title )
Jordy
Climber