In my situation I have a line in which each column represents the revenue of each day for a product, I want to obtain the variance in percentage. For example, In the total revenue of products for those days, I want to know what is the percentage of each day in the total sum.
Ex: Total sum of DATE revenue for the days is 100, and for each day I have values like 20, 40, 10, 30. I need to know what is the percentage of each of these values in the total sum of each product
Accepted Solutions
Hi @Venancioe
Suggesting 2 possible solutions. (used some sample data manually from your screen shot. would have helped if you had shared the data sample file!)
1. Get the next day revenue as an additional field in the script, then use it for calculation of var percentage in the front end.
2. Alternative use "Above" function but you need to switch Date as rows and Product as columns for this to work.
I have attached the qvw file with both options for your review. See the screen shot below.
Regards.
Hi @Venancioe
Two ways of calculating the "NextDateRevenue".
1. In the front end i have used 'Above' function in the chart but has limitations.
2. Script side >> by creating an additional calculated column using the "Previous" function which evaluates the previous record value against the current record value. Make sure the table is sorted in the correct order for this to work ie. Date descending within each Product (ie. sorted by Product first). This way Previous record will always have Date which is cur record date + 1 for each Product. See the screen shot below how i used it in the script.
Hope this helps.
Regards.
thanks Tincholiver
Its working, but I think is comparing on the vertical(complete column) for the current date.
What I want is to compare the product revenue of Previous day with product revenue of Next day and so on
Like: AIRTIME ADVANCE
Compare revenue of 8/3/2021 with 8/4/2021 ,
8/4/2021 with 8/5/2021 and so on
I agree with Steve Joyce, you are not looking for how much each bundle represents over the total percentage, you need to know how much it changed compared to the previous day. For that you will have to calculate the following:
1. SUM ({<DATE = {'$ (= max (DATE))'}>} REVENUE) this is the last registered value
2.SUM ({<DATE = {'$ (= Date (max (DATE) -1))'}>} REVENUE) this is the value of the previous day
3.(SUM ({<DATE = {'$ (= max (DATE))'}>} REVENUE) / SUM ({<DATE = {'$ (= Date (max (DATE) -1))'}>} REVENUE))-1
this is the percentage difference you are looking for.
Take the date dimension from the graph and generate each expression per day that you need, you can do it for the last 30 days or something like that.
If you show us how the data is loaded in the table, maybe another solution can be applied
something like this:
