How to load year to date sales into script from excel data?

[#1]

Hello Dears,

I have to load the year to date sales from the data in TestData.xlsx into the scripting itself.

In the Qlikview document, I have been able to calculate and display the year to date sales in a textbox when user clicks on PeriodCounter. However, i need to be able to load year to date sales in the scripting itself as follows:

DataTable:

LOAD Year,
Month,
YearMonth,
PeriodCounter,
Country,
Supplier,
Item,
Sales//,
//YearToDate for Sales here
FROM

(ooxml, embedded labels, table is Sheet1);

Or i could use the loaded data above from DataTable above to load another table in the script with same list of columns and then calculate the YearToDate sales in a for loop or using any algorithm. But it needs to be loaded in the script itself if possible as a table with the other columns

Please find attached the test data and my qlikview application. Any help appreciated , thanks

[#2]

A little precision when being loaded or imported, the year to date sales for 2014 should be separate from the year to date sales for 2015.

[PrashantSangle]

Hi,

As per my understandig For Year to Date.

You want for Current Year Year to Date is from 201501 to 201505 sum sales which is 900

then try below script

DataTable:
LOAD Year,
Month,
YearMonth,
PeriodCounter,
Country,
Supplier,
Item,
Sales//,
//YearToDate for Sales here
FROM

(ooxml, embedded labels, table is Sheet1);

Left Join
LOAD Year,
Sum(Sales) as CYYTD
Resident DataTable where Year=Year(Today) Group By Year;

Regards

[#4]

Hi Ashley

Does the year to date sales need to be cumulative month by month? ie. 300 for January 2015, 450 for February 2015, 600 for March 2015?

If so, you can start simple and use the accumulation option in the chart properties.  This will work fine if users want to look at the sale year by year ie. they select 2014 then select 2015

Please see attached screenshot of chart properties.  If you create a chart with the month as a dimension and an expression which is Sum(Sales) and then select 'Full Accumulation' this should give you what you need.

[morganaaron · Accepted solution]

Not very elegant (and I'm sure there's a better way to do it) but you could use something like:

For i = 1 to 12

YTDTemp:
Load
Year,
$(i) as Month,
Sum(Sales) as YTDSales
Resident YearToDate
Where Month <= $(i)
Group by Year;

Next i;

Join(YearToDate)
Load
Year,
Month,
YTDSales
Resident YTDTemp;

Drop Table YTDTemp;

That's assuming you wanted the value of the Sales as it was at each period rather than an overall YTD figure simply against the year, which is much simpler:

Join(YearToDate)
Load
Year,
Sum(Sales) as YTDSales
Resident YearToDate
Group by Year;

I'm sure someone has a better solution though!

[#6]

Hi Ashley

Please see following link for an in depth discussion of this topic

Calculating rolling n-period totals, averages or other aggregations

Aaron - your solution is as elegant as you can get for rolling totals.  You can either do what you have done and pre-aggregate the sales in the script, or you can use the same approach to just create a link table that details which month numbers are included in each month's rolling total ie. January is months 1, February is months 1 and 2, March is months 1, 2 and 3

[morganaaron]

I think what James mentions is the more sensible solution for this, though this doesn't quite achieve your need to have the pre-calculated sales in the script. Is there a reason why you need it like this?

As a secondary point, if you did want to separate 2014 and 2015 as mentioned, you could create separate loops for the year by adding another for next loop based on the year counter.

[#8]

Hi James,

Yes indeed the year to date sales need to be cumulative month by month ie. 300 for January 2015, 450 for February 2015, 600 for March 2015, etc.

For January 2014 it will be 50 and February 2014 it will be 50 + 100 = 150, etc

Thanks lots for your reply. I'm gonna check it right now. The requirement is to have the cumulation to be loaded as part of the scripting when importing the table.

[#9]

Hello,

Thanks for yoru reply. Im getting error that field Today does not exist. Should i modify something else in the script?

[#10]

I think Max meant to type...

where Year=Year(Today()) Group By Year

Today() is a function and not a field name.

But this will filter the results to only the current year...