topic Re: Year to Date figure in QlikView

Year to Date figure

guillaume_gorli — Fri, 12 Feb 2016 14:19:04 GMT

Dear All,

I am trying to build a Yeart to date figure based on the user selection.

Let me explain :

I am using below test table with two colums : Date and Sales

Date	Sales
01/01/2015	15
03/01/2015	20
16/02/2015	1
17/02/2015	7
19/02/2015	12
01/03/2015	20
01/04/2015	28
25/05/2015	85
01/01/2016	10
10/01/2016	15
01/02/2016	20
10/02/2016	10
25/02/2016	35
01/03/2016	15
01/04/2016	17
01/05/2016	35
01/05/2016	40

Based on the period selection i need to sum this up in a table showing sales by month, sales by month of the previous year, year to date sales and year to date sales of the previous year.

No issue to get the sales per month as well as the sales for the period of the previous year...

But i can't manage to get the year to date results.

If year 2016 is selected, final output should be like below table :

Atteched is .qvw as well as excel database for the ones who want to help.

Thanks in advance

Guillaume

Re: Year to Date figure

sunny_talwar — Fri, 12 Feb 2016 14:22:55 GMT

Try these expressions:

If(Sum (Sales) > 0, RangeSum(Above(Sum(Sales), 0, RowNo())))

=If(Sum (Sales) > 0, RangeSum(Above(Sum({$<[Année]={$(=only(Année)-1)},Mois= {JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC}>}Sales), 0, RowNo())))

Re: Year to Date figure

guillaume_gorli — Fri, 12 Feb 2016 14:36:43 GMT

Thanks Sunny for the quick feedback.

The table results are correct although when i do select a month, it's not working :

Where it should looks like :

output table is shown as below :

Guillaume

Re: Year to Date figure

sunny_talwar — Fri, 12 Feb 2016 14:39:12 GMT

Change Sale Y-1 formula to this:

=If(Sum (Sales)> 0,Sum({$<[Année]={$(=only(Année)-1)},Mois= {JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC}>}(Sales)))

Re: Year to Date figure

guillaume_gorli — Fri, 12 Feb 2016 14:46:54 GMT

Thanks Sunny, it's perfect !

Re: Year to Date figure

sunny_talwar — Fri, 12 Feb 2016 14:54:40 GMT

Here is another method:

LOAD Date,

Sales

FROM

[TEST YTD.xlsx]

(ooxml, embedded labels, table is Feuil1);

Join (Feuil1)

LOAD Date(AddYears(Date, 1)) as Date,

Sales as Previous_Sales

Resident Feuil1;

Expression1:

1) Sum (Sales)

2) Sum (Previous_Sales)

3) If(Sum (Sales) > 0, RangeSum(Above(Sum(Sales), 0, RowNo())))

4) If(Sum (Sales) > 0, RangeSum(Above(Sum({<Mois>}Previous_Sales), 0, RowNo())))

Output will still stay the same.

Re: Year to Date figure

guillaume_gorli — Thu, 18 Feb 2016 14:41:20 GMT

Dear sunny, i am creating a new topic as i am still struggling with this app...I will define more precisely what are the final output needed.

Re: Year to Date figure

sunny_talwar — Thu, 18 Feb 2016 14:49:45 GMT

Sounds good