Percentage Variance on Table

[Venancioe]

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

[Venancioe]

Many thanks @tincholiver 

I made the changes and analyses on the comments and formula. I'm getting this details

formula used :

(SUM ({<DATE = {'$(=max(REV_DATE))'}>} REVENUE) / SUM ({<DATE = {'$(=Date(max(REV_DATE)-1))'}>} REVENUE))-1

[tincholiver]

my pleasure to help you solve it.
If you found the solution you were looking for, please accept it as a solution to close the case.

Regards

[Venancioe]

Isn't solved yet, Because I'm getting Only Zero Values on the percentages.

What I've show is that the Date isn't working as a reference of the revenue field

[Venancioe]

Find here the Sample App, so you can check.

Many thanks mates

[tincholiver]

You must take the DATE field as a dimension since each expression will return the value you are looking for. You must make an expression for each day you want to calculate

1. Ex. Percentaje diferencie between last and previous date

(

(SUM({<DATE={'$(=max(DATE))'}>}REVENUE) / Aggr(NODISTINCT SUM({<DATE={'$(=max(DATE))'}>}REVENUE),DATE))

/

(SUM({<DATE={'$(=Date(max(DATE)-1))'}>}REVENUE) / Aggr(NODISTINCT SUM({<DATE={'$(=Date(max(DATE)-1))'}>}REVENUE),DATE))

)-1

2. Diference between yesterday and previous date

(

(SUM({<DATE={'$(=Date(max(DATE)-1))'}>}REVENUE) / Aggr(NODISTINCT SUM({<DATE={'$(=Date(max(DATE)-1))'}>}REVENUE),DATE))

/

(SUM({<DATE={'$(=Date(max(DATE)-2))'}>}REVENUE) / Aggr(NODISTINCT SUM({<DATE={'$(=Date(max(DATE)-2))'}>}REVENUE),DATE))

)-1

and so on until you have all the days you are looking for.

with DATE as dimension you will get this

calculating each day with an expersion you will get this

[Venancioe]

Hi.

Can you please test those formulas on the app? I'm not getting success 

[tincholiver]

sorry there was an error in the epression.

I don't have qlik sense. I don't know if you can see my app.

However, put the next in your table:

Dimension: PRODUCT.

Expresion: 

1. Last day vs previous day

(

SUM({<DATE={'$(=Date(max(DATE)))'}>}REVENUE) / SUM({<DATE={'$(=Date(max(DATE)-1))'}>}REVENUE)

)-1

2. Before:

(

SUM({<DATE={'$(=Date(max(DATE)-1))'}>}REVENUE) / SUM({<DATE={'$(=Date(max(DATE)-2))'}>}REVENUE)

)-1

and so on with each day you want to get that variation

[NageshSG · Accepted solution]

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.

[Venancioe]

Hi. 

Find the sample

[NageshSG]

Hi @Venancioe 

Thanks for the sample data.

I refreshed the qvw file (revised file attached below) sent earlier with minor changes based on the sample data now provided.

Save the qvw file and the excel file in the same location and run it, in case you want to update with actual data.

You have the option choose either of the solutions suggested by me earlier.

Hope this works!

Regards.