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

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.