Qlikview calculates the sales data from the beginning of the year to today.

The sum of sales from the beginning of the new fiscal year to today is a common demand of many students. There are two ideas:

1. You can generate a field named ytdflag when loading data, that is, this field is 1 in the current fiscal year; otherwise, it is 0. Therefore, sum (sales * ytdflag) can be easily used in the report to obtain the desired KPI.

The script for load data is as follows:

SalesData:LOADNum(ID) as ID,Date(Date) as Date,Month,Num(Year) as Year,Num(Sales) as Sales,InYearToDate(Date(Date), Today(), 0, 10) as YTDFlagInline [ID, Date, Month, Year, Sales1, 2012-1-1, 2012-1, 2012, 202, 2012-1-1, 2012-2, 2012, 213, 2012-1-1, 2012-3, 2012, 234, 2013-1-1, 2013-1, 2013, 345, 2013-2-1, 2013-2, 2013, 356, 2013-3-1, 2013-3, 2013, 467, 2014-1-1, 2014-1, 2014, 278, 2014-2-1, 2014-2, 2014, 309, 2014-3-1, 2014-3, 2014, 4910, 2013-10-1, 2013-10, 2013, 5011, 2013-11-1, 2013-11, 2013, 5112, 2013-12-1, 2013-12, 2013, 52];

2. Of course, you can also use the formula in the report. The formula is = sum (sales * inyeartodate (date, today (), 0, 10) *-1 ).

The results obtained in the above two methods are 259. When the data volume is large, the calculation is better first, so you do not have to implement all the calculations on the front end. This will affect the performance.

 

The key method inyeartodate has four parameters, meaning:

Whether the first parameter and the second parameter are in the same year;

The third parameter indicates the number of years required for the second parameter, and 0 indicates that the first parameter is directly compared with the second parameter (the year of the second parameter is reduced by 0 ), -1 indicates that the year of the second parameter needs to be reduced by 1, and then compared with the first parameter, and so on. That is to say, the year of the second parameter must be subtracted from the year represented by the third parameter, and then compared with the first parameter;

The fourth parameter is optional, indicating that the year starts from the nth month, and the fourth parameter above is 10, indicating that the first day of the New Year is counted as the first day of the new year, whether the first parameter is the same as the second parameter in the first year of October 1.

If yes, the formula returns-1; otherwise, it is 0.

 

In the example above, October 1 is the first day of the new year. Because the third parameter is 0, the first parameter is directly compared with the second parameter, indicates whether the value in the date field is the same as today's one year starting from January 1, October 1. For example, 2013-11-1 is, And 2013-09-31 is not.

Inyeartodate (date, today (),-1, 10) indicates that the field in date is compared with the year of last year (today () minus one, is it in the year starting from January 1, October 1.

 

My language is not very good. If you are not clear about it, please leave a message!

Qlikview calculates the sales data from the beginning of the year to today.