Division of two sums to get percentage

[#1]

Hi All,

I have a pivot table in which i calculate the total depositors and then i need to calculate the % of the total depositors.

The total depositors are calculated as follows;

Sum({<TRANSACTION_HISTORY.VALUE_DATE= {"<$(=CALENDAR.VAR_DATE_ONE)"}>} If(Left(GENERAL_LEDGER.GL_CODE,1)<>1 and Left(GENERAL_LEDGER.GL_CODE,2)<>21 and Left(GENERAL_LEDGER.GL_CODE,2)<>28,TRANSACTION_HISTORY.CV_AMOUNT))

Whereas the % of total depositors is caluculted as;

Sum

({<TRANSACTION_HISTORY.VALUE_DATE= {"<$(=CALENDAR.VAR_DATE_ONE)"}>} If(Left(GENERAL_LEDGER.GL_CODE,1)<>1 and Left(GENERAL_LEDGER.GL_CODE,2)<>21 and Left(GENERAL_LEDGER.GL_CODE,2)<>28,TRANSACTION_HISTORY.CV_AMOUNT))/Sum({<TRANSACTION_HISTORY.VALUE_DATE= {"<$(=CALENDAR.VAR_DATE_ONE)"}>} If(Left(GENERAL_LEDGER.GL_CODE,1)<>1 and Left(GENERAL_LEDGER.GL_CODE,2)<>21 and Left(GENERAL_LEDGER.GL_CODE,2)<>28,Sum(total TRANSACTION_HISTORY.CV_AMOUNT)))

The problem occurs with the % of total depositors wherein the field is shown with a '- '. This problem occurs because i have added code to obtain amounts as per current date.

Please can someone help me with this. Many Thanks.

Best Wishes & Regards.

[#2]

Hi,

In QlikView, if you are using SET analysis then there is no need to use IF function.

In your case, you are using both SET and IF.

You can apply SET for General_Ledger.GL_Code also.

Then try to get ratio.

Still if problem persists, share sample data.

Happy Thoughts

[#3]

Hi Ravi,

I'm sorry but I dont follow.

Im getting the ratio if i do it as per a fixed date, but when i link it to a calender object and try to generate the data per different dates the ni get null values.

Since i pull the data using qlikview connection to my company server, it would be difficult to replicate the data and share it.

Regards.

[#4 · Accepted solution]

Hi,

I have the solution and instead of going through such a long process.

I have found the solution and we can use the following;

Sum(Amount) / Sum(total Amount)

So in my expression it would appear as;

Sum({${<TRANSACTION_HISTORY.VALUE_DATE= {"<$(=CALENDAR.VAR_DATE_ONE)"}>} If(Left(GENERAL_LEDGER.GL_CODE,1)<>1 and Left(GENERAL_LEDGER.GL_CODE,2)<>21 and Left(GENERAL_LEDGER.GL_CODE,2)<>28,TRANSACTION_HISTORY.CV_AMOUNT))

/

Sum({${<TRANSACTION_HISTORY.VALUE_DATE= {"<$(=CALENDAR.VAR_DATE_ONE)"}>} total If(Left(GENERAL_LEDGER.GL_CODE,1)<>1 and Left(GENERAL_LEDGER.GL_CODE,2)<>21 and Left(GENERAL_LEDGER.GL_CODE,2)<>28,TRANSACTION_HISTORY.CV_AMOUNT))