topic Re: Calculating total in QlikView

Calculating total

yashcena — Wed, 23 Aug 2017 11:10:56 GMT

I'm working on an app which has the following fields: Functional Area, Sub Functional Area and 3 different costs- Actuals (eUSD_Amt), budget(bUSD_Amt) and outlook (oUSD_Amt).

I have data for all 12 months of 2017 for outlook. But for Actuals, I have data till July only.

I have to show the sum of Actuals for all the 12 months. Since I have data only till July, I need to add the corresponding months' outlook data for missing months.

Something like this:

Jan(eUSD_Amt)+ Feb(eUSD_Amt) + Mar(eUSD_Amt)+ Apr(eUSD_Amt)+ May(eUSD_Amt)+ Jun(eUSD_Amt)+ Jul(eUSD_Amt)+ Aug(oUSD_Amt)+ Sep(oUSD_Amt)+ Oct(oUSD_Amt)+ Dec(oUSD_Amt)

We would be getting the remaining months Actuals (eUSD_Amt) in due course of the remaining part of the year. Till then, I need to use the outlook (oUSD_Amt) for unavailable months' data to calculate the total Actuals for entire 12 months.

I hope I have made my requirement clear.

Thank you,

Yash

Re: Calculating total

Anil_Babu_Samineni — Wed, 23 Aug 2017 11:19:20 GMT

Try this

RangeSum(sum({<monthfield ={'jan','feb','mar','apr','may','jun'}>} TOTAL eUSD_amt), sum({<monthfield = {'jul','aug','sep','oct','dec'}>} TOTAL oUSD_amt))

Re: Calculating total

niclaz79 — Wed, 23 Aug 2017 11:25:18 GMT

Hi, in your example the actuals for future months is always 0 which you can use.

This expression should work:

if(Sum(eUSD_Amt) > 0, Sum(eUSD_Amt), sum(oUSD_Amt))

jf you set a color expression with the same rules you can differentiate visually also on if you are looking at an actual or a forecast.

Re: Calculating total

yashcena — Wed, 23 Aug 2017 11:25:28 GMT

It did not work.

Re: Calculating total

sunny_talwar — Wed, 23 Aug 2017 11:25:53 GMT

May be this

=Sum({<Year = {$(=Max(Year))}>}Aggr(If(Sum(eUSD_Amt) = 0, Sum(oUSD_Amt), Sum(eUSD_Amt)), Year, Month))

Re: Calculating total

Anil_Babu_Samineni — Wed, 23 Aug 2017 11:28:07 GMT

I am using mobile now? Just guessing? Looks like sunny share the image.

Re: Calculating total

vinieme12 — Wed, 23 Aug 2017 11:29:40 GMT

Or may be just this!

=sum(AGGR(if(sum(eUSD_Amt)=0,sum(oUSD_Amt),sum(eUSD_Amt)),Year,Month))

Re: Calculating total

sunny_talwar — Wed, 23 Aug 2017 11:30:53 GMT

The total will be incorrect unless the OP is willing to switch to straight table

Re: Calculating total

yashcena — Wed, 23 Aug 2017 12:45:46 GMT

Thank you...that worked!

Re: Calculating total

yashcena — Tue, 12 Sep 2017 09:06:55 GMT

Hi Sunny,

What would be the expression if I want to show the actual amount for individual months?

For example, if Actual data is not available for the month of January, it should show the corresponding Outlook Amount.

I have a new requirement where in along with Actual total, I need to show the individual months in text boxes.

Thanks,

Yash

Re: Calculating total

sunny_talwar — Tue, 12 Sep 2017 11:24:29 GMT

For example, if Actual data is not available for the month of January, it should show the corresponding Outlook Amount.

Isn't that what it is doing today? I am not sure I completely understand your need.... Can you elaborate?