topic Re: Get Cumulative sum upto particular time selection in QlikView

Get Cumulative sum upto particular time selection

Anonymous — Thu, 25 Jun 2015 17:14:57 GMT

Hi All

I am new to qlikview and am trying to build a dashboard using it.

The data that I have is time-based data(like sales transaction data of over a few years).

I would like to show my cumulative sales revenue numbers uptil a selected time selection( year and month derived out of the sale time) but I am not sure as to how to get the cumulative sum from the 1st transaction upto that particular time selection.

Could anybody please help me out?

Thanks

Re: Get Cumulative sum upto particular time selection

ramoncova06 — Thu, 25 Jun 2015 18:43:02 GMT

can you provide an example of what exactly you need done

Re: Get Cumulative sum upto particular time selection

Thu, 25 Jun 2015 18:46:49 GMT

Hi,

It depends on how you are planning to show, like which chart you are going to use.

Normally if you want date filter than you can use variable and Calendar object. Post your app.

Re: Get Cumulative sum upto particular time selection

Anonymous — Fri, 26 Jun 2015 14:21:04 GMT

Hi Ramon and Navdeep

Thanks for your quick response.

Below is a snapshot of the sample data.

TxnID	Sales_Date	Revenue	Cumulative_Revenue
1	2-Jan-10	10	10
2	18-Apr-10	10	20
3	19-Apr-10	10	30
4	25-Apr-10	10	40
5	9-Jul-10	10	50
6	2-Jan-11	10	60
7	18-Apr-11	10	70
8	19-Apr-11	10	80
9	25-Apr-11	10	90
10	9-Jul-11	10	100

I use the Sales_Date field to generate the year and the month.

Now the metric that I want to reflect is the cumulative sales number, but that is not actually in data( I want qlikview to calculate and then display it).

The problem(as from my understanding) is that, on selecting a particular time(year/month or both), qlikview will display only the sum of revenues that belong to that particular time.

like on selecting Apr 2011, it will display 30, but what i want it to display is 90, which is the cumulative revenue sum, from the 1st TXn till the last TXn of the selected month.

Is there some function in qlikview that can help me with this kind of aggregation ?

Thanks

Tarang

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Fri, 26 Jun 2015 14:43:38 GMT

Something like this?

Used two methods:

1) RangeSum Method

=RangeSum(Above(Sum({<Sales_Date = {"$(='<=' & Date(Max(Sales_Date)))"}, MonthYear = , Month = , Year = >}Revenue), 0, RowNo()))

2) Full Accumulation Method

Attaching the application for your convenience

HTH

Best,

Sunny

Re: Get Cumulative sum upto particular time selection

Anonymous — Wed, 01 Jul 2015 19:58:02 GMT

Hi sunindia

Thank you very much for your response. Yes, that's what I am kind of expecting, just that I am using Month and Year as separate columns.

However, can you please explain the rangesum formula ?

Also, I am using text objects to display the aggregated number(the last Cumulative_Response entry for the selected time selection) and I cannot find the full accumulation option in the dialogue box. Could you also help me with this ?

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Wed, 01 Jul 2015 20:04:36 GMT

To get it in text box, you can use this formula:

=FirstSortedValue(Aggr(RangeSum(Above(Sum({<Sales_Date = {"$(='<=' & Date(Max(Sales_Date)))"}, MonthYear = , Month = , Year = >}Revenue), 0, RowNo())), Sales_Date), -Sales_Date)

I will answer you first question in a bit.

Best,

Sunny

Re: Get Cumulative sum upto particular time selection

Anonymous — Wed, 01 Jul 2015 21:05:57 GMT

I am getting error while applying this formula.

So, previous one was a general idea, below is the data I am actually using:-

ID	Subs_Start_Date	Subs_ Expiry_Date	Type	Sale	Tax	Expenses	Customer_ID	Score
1	1/1/2014	1/1/2015	A	23154	7010	926.16	C1	4630.8
2	1/1/2014	1/1/2015	C	101834	27070	8146.72	C2	40733.6
3	1/1/2014	1/1/2015	A	27990	2153	1119.6	C3	5598
4	1/1/2014	1/1/2015	B	128930	30129	9025.1	C4	45125.5
5	1/2/2014	1/2/2015	A	32595	16110	1629.75	C5	8148.75
6	4/23/2014	4/23/2015	B	456879	216719	13706.37	C1	4630.8
7	4/23/2014	4/23/2015	C	267358	13253	18715.06	C2	40733.6
8	4/24/2014	4/24/2015	C	231944	21607	18555.52	C3	5598
9	4/24/2014	4/24/2015	B	359420	95611	35942	C4	45125.5
10	4/24/2014	4/24/2015	C	153443	8615	3068.86	C5	8148.75

There are multiple purchases for a customer on different dates during the year. I am trying to calculate to net score for the customers upto the current time selection(or for all data, if nothing is selected). But in my data the score data is repeated (notice repeated scores for each customer_id) and I have to use score value of each customer only single time to calculate the total.

I am using a text box to display the number. The formula that I am using is

=Num(Sum(aggr(Min([Score]),[Customer_ID])),'$ #,##0.00 ')

(using min/max/avg all will yield the same result).

So, when I replace the "revenue" and "Sales_Date" with the above formula and Subs_ Expiry_Date respectively, I simply get a "-" in the text box with no error or explanation.

Is the right thing and would it work while just having to replace the text in the above fashion or do I need some different approach?

Thanks

Tarang

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Wed, 01 Jul 2015 21:11:41 GMT

I will look at it once I reach home.

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Wed, 01 Jul 2015 22:46:47 GMT

I have few questions here:

1) Are we calculating the Score Field in the script or is that given in the database?

2) What is the output you are expecting to see? Tell me the numerical values of what you are looking for.

HTH

Best,

Sunny

Re: Get Cumulative sum upto particular time selection

Anonymous — Thu, 02 Jul 2015 13:33:09 GMT

The score field is given in the data posted above,(in the database) you would have to use the horizontal scroll bar for that. What we have to calculate is the total sum of scores for each customer(excluding the duplicated values).

The output that I am expecting to see here is 4630.8 + 40733.6 + 5598 + 45125.5 + 8148.75 = 104236.65

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Thu, 02 Jul 2015 13:48:13 GMT

Try this:

=Num(Sum(Aggr(Avg([Score]),[Customer_ID])),'$ #,##0.00')

Output:

Re: Get Cumulative sum upto particular time selection

Anonymous — Thu, 02 Jul 2015 14:03:11 GMT

=Num(Sum(aggr(Min([Score]),[Customer_ID])),'$ #,##0.00 ')

This is the one that I already tried, and it work( works for avg/min/max functions because all the score values for a customer are same), but along with it, I want cumulative aggregation of score upto a particular selected time.

The dates are a bit skewed in the previous data set, so posting a bit refined one below.

Now for the dataset posted below, if I select the year as 2014 and month as Mar, so scores(single and unique) for each customer should get summed upto that time-point and here it would be-

45125.5 + 40733.6 + 5598 = 91457.1 (here customers C1, C2 and C3 are selected and each of their scores get summed up only single time).

ID Subs_Start_Date Subs_ Expiry_Date Type Sale Tax Expenses Customer_ID Score

1 1/1/2014 1/1/2015 A 23154 7010 926.16 C1 45125.5

2 1/20/2014 1/20/2015 C 101834 27070 8146.72 C2 40733.6

3 2/13/2014 2/13/2015 A 27990 2153 1119.6 C3 5598

4 3/16/2014 3/16/2015 B 128930 30129 9025.1 C1 45125.5

5 4/20/2014 4/20/2015 A 32595 16110 1629.75 C5 8148.75

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Thu, 02 Jul 2015 15:55:00 GMT

Try this:

=Num(Sum({<Month = {"$(='<=' & Max(Month))"}, Year = {"$(=Max(Year))"}>} Aggr(Avg({<Month = {"$(='<=' & Max(Month))"}, Year = {"$(=Max(Year))"}>}[Score]),[Customer_ID])),'$ #,##0.00')

Output:

I hope this is what you wanted?

Also attaching the qvw for your reference.

Best,

Sunny

Re: Get Cumulative sum upto particular time selection

Anonymous — Thu, 02 Jul 2015 16:46:24 GMT

Yes It works.!
Thank you very much for your help.
Just could you please explain the formula?

Thanks
Tarang

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Thu, 02 Jul 2015 16:54:56 GMT

Sure

//Ignoring this part because its self-explantory

=Num(

//Once I get an imaginary table formed by the Aggregate function, I am summing all the observations where the score is again greater than or equal to max month for the max year
Sum({<Month = {"$(='<=' & Max(Month))"}, Year = {"$(=Max(Year))"}>}

// Aggregating over the Customer_ID because there is possibility of repetition of Score

Aggr(

// Averaging the Score for greater than or equal to max month for the max year only (this may change based on your requirement

Avg({<Month = {"$(='<=' & Max(Month))"}, Year = {"$(=Max(Year))"}>}[Score]),

//Ignoring this part because its self-explantory

[Customer_ID])),

//Ignoring this part because its self-explantory

'$ #,##0.00')

Does the above explanation helps? If not then make a straight table with Customer_ID and use the expression:

Avg({<Month = {"$(='<=' & Max(Month))"}, Year = {"$(=Max(Year))"}>}[Score])

This table is your imaginary table and then select Total Mode as Sum of Rows and you will see that this total will always match the total from the expression I gave you earlier because the two things are equal. Aggr creates an imaginary table (which looks like straight table) and the Sum and the set analysis statement after Aggr is the same thing as Total Mode as Sum of Rows.

HTH

Best,

Sunny

Re: Get Cumulative sum upto particular time selection

Anonymous — Thu, 02 Jul 2015 17:07:28 GMT

Yes, It definately does. Thanks for your quick reply.

So just to be clear, $ sign just replaces the text inside the () with the respective generated expression, like in the case of expression
Avg({<Month = {"$(='<=' & Max(Month))"}, Year = {"$(=Max(Year))"}>}[Score])

effective final replaced expression would be Avg({Month=(<=December), Year=(2014)}[Score]).

If that assumption is right and if it would had been multi-year data(data above is only for 2014), then i guess Year expression would also had been

Year= {"$(='<=' & Max(Year))"}

is my understanding correct?

Thanks

Tarang

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Thu, 02 Jul 2015 17:10:17 GMT

Yup what you just said is absolutely right. Since we had only one year data, I was not sure if want to sum just one selected year or all previous years. But you got it right that if you need all years you will need Year= {"$(='<=' & Max(Year))"}

Re: Get Cumulative sum upto particular time selection

Anonymous — Thu, 02 Jul 2015 17:15:04 GMT

Thank you very much for your help.!

Re: Get Cumulative sum upto particular time selection

sunny_talwar — Thu, 02 Jul 2015 17:16:30 GMT

No problem.

I am glad I was able to help.

Best,
Sunny