Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
RodrigoRodrigues
New Contributor

Get first entry for each user and sum on MonthYear

I need to get the minimum date for each user, and sum the number of dates that are on the same month/year.

Let me give a quick example this is a sample set: 

image.png

I need a formula that makes a table like this:

image.png

I already got the MonthYear, but I am having trouble to get the myFormula part.

Basically I need to get the MonthYear of the first entry of the user and sum with all other users that have the same MonthYear for their first entry. 

So we have 1 for Dec-2018 (Cesar - 11/12/18 -> First Entry)
2 for Jan-2019 (Julio 07/01/19 -> first entry and Bruno 25/01/19 -> First Entry)

I hope that what I need to achieve is clear.

Thanks!


Tags (3)
Labels (3)
1 Solution

Accepted Solutions
neelamsaroha157
Valued Contributor II

Re: Get first entry for each user and sum on MonthYear

Check the attached.

3 Replies
neelamsaroha157
Valued Contributor II

Re: Get first entry for each user and sum on MonthYear

Check the attached.

RodrigoRodrigues
New Contributor

Re: Get first entry for each user and sum on MonthYear

Thanks for the answer.

It actually worked as expected on your attachment, but I could not make it work on my file.

I have some doubts about your attached file.

image.png

Where are the fields MinDate coming from? I don't understand how this works.

Also, on my file I was using sum and aggr to try to get what I want. Is there a way of doing the same stuff you did on the load using some formula that is not on the load script?

Thanks!

neelamsaroha157
Valued Contributor II

Re: Get first entry for each user and sum on MonthYear

Here is the solution with front end expression.