Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try this:
=Num(Sum(Aggr(Avg([Score]),[Customer_ID])),'$ #,##0.00')
Output:
=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
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
Yes It works.!
Thank you very much for your help.
Just could you please explain the formula?
Thanks
Tarang
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
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
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))"}
Thank you very much for your help.!
No problem.
I am glad I was able to help.
Best,
Sunny