topic Re: How to sum sales by year in QlikView

How to sum sales by year

Anonymous — Mon, 01 Dec 2014 21:43:51 GMT

I am creating a straight table similar to the one below. and I want to pre-calculate sales by year for last 3 years. My fact table has transactional sales, one of the fields is date. what function do I use so that I get sum of sales for each year as a field.

Heade

Re: How to sum sales by year

ecolomer — Mon, 01 Dec 2014 21:52:16 GMT

Is that you need?

Re: How to sum sales by year

ecolomer — Mon, 01 Dec 2014 21:57:58 GMT

or this?

Re: How to sum sales by year

Mon, 01 Dec 2014 22:12:21 GMT

You may want to start dipping your feet into Set Analysis:

Here is the syntax if I am understanding correctly

Expression 1 = Sum({<Year={>=$(=Max(Year(DATEFIELD))}>}Sales)

Expression 2 = Sum({<Year={>=$(=Max(Year(DATEFIELD)-1)}>}Sales)

Expression 3 = Sum({<Year={>=$(=Max(Year(DATEFIELD)-2)}>}Sales)

Re: How to sum sales by year

Anonymous — Mon, 01 Dec 2014 23:52:40 GMT

Thanks everyone. sorry it was not very clear what I was trying to explain. I am trying to create a straight table where I have probably 50 departments (which is my dimension, the image attached is just a sample). the transaction table has records for period between 2011 and 2013. If i do sum(Total Sales Receipts), it will lump the total sale from 2011 - 2013 in 1 column -- as in image 1 below. I want the sales for each department to be split in 3 diff years as in image below. So what should be my AGGR function, (i guess) to make that happen

Re: Re: How to sum sales by year

anbu1984 — Tue, 02 Dec 2014 05:37:56 GMT

Check this app

Re: Re: How to sum sales by year

Anonymous — Tue, 02 Dec 2014 13:27:24 GMT

Anbu, where is the app

Re: Re: Re: How to sum sales by year

anbu1984 — Tue, 02 Dec 2014 13:43:55 GMT

I am able to see the app. I am attaching again.

Re: How to sum sales by year

Anonymous — Tue, 02 Dec 2014 14:20:58 GMT

i can not open the qvw file. can you post the script here

Re: How to sum sales by year

emkabi646 — Tue, 02 Dec 2014 14:21:04 GMT

An other way to solve it, is to use the aggr-Function. Create a Pivot-Table with the two Dimensions Dept. and Year. The Expression will be: aggr(Sum(sales), Year, Dept)

Re: How to sum sales by year

anbu1984 — Tue, 02 Dec 2014 14:25:03 GMT

Load * Inline [

Dept,Year,Sales

A,2011,100

A,2012,200

A,2013,300

B,2011,100

B,2012,200

B,2013,300 ];

Dimension: Dept

Expr1: Sum({<Year={2011}>}Sales)

Expr2: Sum({<Year={2012}>}Sales)

Expr3: Sum({<Year={2013}>}Sales)

Re: How to sum sales by year

Anonymous — Tue, 02 Dec 2014 16:06:21 GMT

Thank you Anbu. Your solution worked perfectly!!!

Martin,

I have posted 2 images below: 1st as Anbu suggested, solved the problem. AGGR, however did not bring the desired result, which is in the 2nd image. I have included a 3rd image to show how my fact table is. because it is a lil different,(look at the date field) it is not putting the values in 3 separate year fields. I want to learn how to solve this using AGGR. I appreciate your help very much. Please comment.

Re: How to sum sales by year

anbu1984 — Wed, 03 Dec 2014 05:41:47 GMT

Check this video to change your pivot table to display years in columns

https://www.youtube.com/watch?v=txgHKIoPQRM

Re: How to sum sales by year

Anonymous — Thu, 04 Dec 2014 21:25:45 GMT

Hi Anbu, I was able to split sales into 3 years, thanks to the link above. However I have ran into diff issues this time. the image below represents my pivot table: for every department where there are more than 1 vendor, the subtotal is not showing totals for that category. For departments with only 1 vendor, it is showing subtotal (which is pointless). May be you can see what I am doing wrong. I have checked the partial sum box but its not working.

Re: Re: How to sum sales by year

anbu1984 — Fri, 05 Dec 2014 05:23:08 GMT

You should get sub-total. Can you post sample app