Get Cumulative sum upto particular time selection

[Anonymous]

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

[Anonymous]

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

[sunny_talwar]

Try this:

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

Output:

[Anonymous]

=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

[sunny_talwar · Accepted solution]

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

[Anonymous]

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

Thanks
Tarang

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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))"}

[Anonymous]

Thank you very much for your help.!

[sunny_talwar]

No problem.

I am glad I was able to help.

Best,
Sunny