Doubt of Aggregation Criterion to create a Measure

[SaebaRyo]

Hello everyone in the community,

 I am trying to create a measure (No. customers) in a company, but I need that the aggregate function is not sum, that is, if we want to analyze the clients that I have in the current year they should not be added since it is not correct . It would be the number of clients in the last month of the current year. The following table shows an example of what we want to do with the customer measure:

	YearMonth	Count(No. customers)
	201909	8
	201910	10
	201911	20
	201912	45
	202001	35
	202002	43
	202003	50
	202004	55
	202005	71
Total Costumer for 2019		42
Total Costumer for 2020		71

As we can see the totals are the last monthly value of years that is loaded. This measurement would be used in both pivot tables and charts. I have been using QlikSense for a few months and I have not been able to do this measurement. In other tools they have aggregation options of the type Time-State rollup that you can apply in the measures.

Thanks to everyone for your support.

A cordial greeting,

[QlikTom]

Hello, it might be helpful to know the original shape of the data you want to transform into this result.  

If I read this correctly, you want to do an accumulation of new customers in the year they came onboard, aggregated to a monthly granularity, and reset the count at the start of the next year. The solution to this challenge has many solutions, but without knowing the fields available to to you, it is hard to suggest one. 

This challenge can be resolved with as little as 2 original source fields:
1) a unique ID for a customer
2) a date field representing when they came onboard. 
3) a Year field in the data model (potentially extracted from the OnBoardingDate) 
4) a YearMonth field in the data model (potentially constructed from the OnBoardingDate in the Data Load Script / Data Manager) 

Year(Date(OnBoardingDate)) & Num(Month(OnBoardingDate),'00') as OnBoardingYearMonth

If you have access to a recent version of Qlik Sense, you should notice a Modifier option on the measure in the table chart.
Select the appropriate options (example below)

My result below. The # Customers measure is only included so the math can be verified.
This is only meant to serve as an example as your challenge may have different parameters. 

Here is a help article on the topic:
https://help.qlik.com/en-US/sense/February2020/Subsystems/Hub/Content/Sense_Hub/Visualizations/Table...

[SaebaRyo]

Hello @QlikTom 

Sorry I took so long to respond, but I was in poor health. Thank you very much for this answer, I especially liked it to create cumulative measurements as seen in the table image.

This solution is close to what I am looking for, but if you look at the image of the table, what I am looking for is that the Customer measure at the level of totals stays with the value of the last month of each year. I highlight it in the image:

If we see the new customer indicator when making totals by Year it should be

Year 2018 YearMonth 201812 #Customer(total) = 47

Year 2019 YearMonth 201812 #Customer(total) = 45

Year 2020 YearMonth 201812 #Customer(total) = 15

Likewise, everything you have explained has been very useful to me in order to make other indicators that I have in my Dashboard.

Thank you very much for the help!

[QlikTom]

If I understand correctly, it sounds like you don't want to SUM the customers across months, by year. Rather, display the sum for the '# customers' for the last available month in any given year. 

I will have to think on that, but I suspect this could be resolved with a Set Expression, or creating a seperate aggregation table in your data load script.