Solved: Total Sales Calculation Problem - Qlik Community

Anonymous · ‎2015-07-31

Hi Everyone,

I have created a straight Table for sales calculation with respect to billing days. I have loaded the Billing and Sales values per year and country wise and I have used the division formula (i.e [Sales to date}/[Billing Days to Date]) to perform the calculation to show the final sales in the respective years. The problem now is, the formula works fine for individual sales vs billing days and gives the correct output. But when it comes to the totals the division operation is not performing well. Can anyone please tell me where I might be doing the mistake. I am attaching the screenshot for reference.

Thanks in advance.

Gysbert_Wassenaar · ‎2015-07-31

Ok, try this expression:

=sum(aggr(if(Year=year(Date1),

sum({<Date={">=$(=vStartDate)<=$(=vEndDate)"},year=>}Sales),

sum({<Date={">=$(=Vprestdate)<=$(=Vpreendate)"},year=>}Sales)

),Country,Year))

/

sum(aggr(sum({<Date1={">=$(vStartDate)<=$(vEndDate)"}>}[Billing Day]),Country,Year))

talk is cheap, supply exceeds demand

View solution in original post

awhitfield · ‎2015-07-31

Hi uday,

can you upload a sample qvw and source data please?

Andy

Kushal_Chawda · ‎2015-07-31

You need to use the Aggr function which will aggregate the values based on dimension.

As you are using the cyclic group, you can use Aggr function with Getcurrentfield fucntion

Try,

=sum(aggr(sum([Sales to date]),$(=GetCurrentField(YourCyclicGroupName))))/

sum(aggr(sum([Billing Days to date]),$(=GetCurrentField(YourCyclicGroupName))))

Gysbert_Wassenaar · ‎2015-07-31

Try setting the Total mode of that expression to Sum of rows instead of Expression Total.

talk is cheap, supply exceeds demand

Anonymous · ‎2015-07-31

I have set the total mode as sum of rows for Sales to date and Billing days to date, but for the final sales calculation I have set the total mode to Expression total, as I want even the total of "Final Sales Calculation" to be the divided value of the "Billing days to date total " and "Sales to date Total". If I set all of them to sum of rows, I get only the sum of Final sales calculation which is not the requirement.

Anonymous · ‎2015-07-31

Hi Kush,

The expression you gave did not work. Instead it is returning the nulls.

Gysbert_Wassenaar · ‎2015-07-31

Change your expression to: sum([Sales to date])/sum(Billing Days to Date])

talk is cheap, supply exceeds demand

Kushal_Chawda · ‎2015-07-31

UdayKiran‌ it should work. Have you correctly entered your cyclic group name?

or please share the screenshot of your expression

Report Inappropriate Content · ‎2015-07-31

In total mode Keep sum of rows for the expression: [Sales to date}/[Billing Days to Date]) and for other exprs keep Expressions Total and try..

Anonymous · ‎2015-07-31

It has returned NULL.