Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tarang_dawer
Contributor II
Contributor II

Get Cumulative sum upto particular time selection

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

1 Solution

Accepted Solutions
sunny_talwar

Try this:

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

Output:

Capture.PNG Capture.PNG

Capture.PNG

Capture.PNG

I hope this is what you wanted?

Also attaching the qvw for your reference.

Best,

Sunny

View solution in original post

19 Replies
ramoncova06
Specialist III
Specialist III

can you provide an example of what exactly you need done

Not applicable

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.

tarang_dawer
Contributor II
Contributor II
Author

Hi Ramon and Navdeep

Thanks for your quick response.

Below is a snapshot of the sample data.

   

TxnIDSales_DateRevenueCumulative_Revenue
12-Jan-101010
218-Apr-101020
319-Apr-101030
425-Apr-101040
59-Jul-101050
62-Jan-111060
718-Apr-111070
819-Apr-111080
925-Apr-111090
109-Jul-1110100

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

sunny_talwar

Something like this?

Capture.PNG

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

Capture.PNG

Attaching the application for your convenience

HTH

Best,

Sunny

tarang_dawer
Contributor II
Contributor II
Author

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 ?

sunny_talwar

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

tarang_dawer
Contributor II
Contributor II
Author

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_DateSubs_ Expiry_DateTypeSaleTaxExpenses Customer_IDScore
11/1/20141/1/2015A231547010926.16C14630.8
21/1/20141/1/2015C101834270708146.72C240733.6
31/1/20141/1/2015A2799021531119.6C35598
41/1/20141/1/2015B128930301299025.1C445125.5
51/2/20141/2/2015A32595161101629.75C58148.75
64/23/20144/23/2015B45687921671913706.37C14630.8
74/23/20144/23/2015C2673581325318715.06C240733.6
84/24/20144/24/2015C2319442160718555.52C35598
94/24/20144/24/2015B3594209561135942C445125.5
104/24/20144/24/2015C15344386153068.86C58148.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

sunny_talwar

I will look at it once I reach home.

sunny_talwar

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