topic Re: Percentage Variance on Table in QlikView

Percentage Variance on Table

Venancioe — Tue, 17 Aug 2021 18:15:18 GMT

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

Re: Percentage Variance on Table

stevejoyce — Wed, 18 Aug 2021 09:37:50 GMT

Your denominator would be Sum(TOTAL <PRODUCT>} Revenue) .

Obviously your existing revenue calculation is the numerator.

Re: Percentage Variance on Table

Venancioe — Wed, 18 Aug 2021 12:52:26 GMT

Hi Stevejoyce

Is not working as expected, it showing the same values for entire table

Re: Percentage Variance on Table

tincholiver — Wed, 18 Aug 2021 13:22:39 GMT

TRY THIS

SUM(REVENUE)
/
Aggr(NODISTINCT SUM(REVENUE),DATE)

Re: Percentage Variance on Table

Venancioe — Wed, 18 Aug 2021 20:24:49 GMT

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

Re: Percentage Variance on Table

Kushal_Chawda — Wed, 18 Aug 2021 21:47:50 GMT

@Venancioe share sample data with expected output

Re: Percentage Variance on Table

Venancioe — Thu, 19 Aug 2021 19:36:39 GMT

Hi @Kushal_Chawda

Find here the sample. I want something like this one marked with red

Re: Percentage Variance on Table

Kushal_Chawda — Fri, 20 Aug 2021 10:40:03 GMT

@Venancioe how % calculated?

Re: Percentage Variance on Table

stevejoyce — Fri, 20 Aug 2021 11:01:28 GMT

Your initial calculation you said "percentage of each of these values in the total sum of each product". This sounds like a different calculation no?

You can use before/after to calculate referencing other columns

like sum(expression) - before(sum(expression)) -> difference between today's date and previous date.

Re: Percentage Variance on Table

tincholiver — Fri, 20 Aug 2021 12:17:22 GMT

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:

Re: Percentage Variance on Table

Venancioe — Fri, 20 Aug 2021 23:28:58 GMT

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

Re: Percentage Variance on Table

tincholiver — Sat, 21 Aug 2021 03:33:07 GMT

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

Re: Percentage Variance on Table

Venancioe — Sat, 21 Aug 2021 18:03:02 GMT

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

Re: Percentage Variance on Table

Venancioe — Sat, 21 Aug 2021 18:01:12 GMT

Find here the Sample App, so you can check.

Many thanks mates

Re: Percentage Variance on Table

tincholiver — Sun, 22 Aug 2021 12:22:45 GMT

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

Re: Percentage Variance on Table

Venancioe — Sun, 22 Aug 2021 13:13:27 GMT

Hi.

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

Re: Percentage Variance on Table

tincholiver — Sun, 22 Aug 2021 14:07:03 GMT

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

Re: Percentage Variance on Table

NageshSG — Sun, 22 Aug 2021 16:36:01 GMT

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.

Re: Percentage Variance on Table

Venancioe — Tue, 24 Aug 2021 10:46:52 GMT

Hi.

Find the sample

Re: Percentage Variance on Table

NageshSG — Tue, 24 Aug 2021 11:23:15 GMT

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.