topic Re: Total Sales Calculation Problem in QlikView

Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 09:04:30 GMT

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.

Re: Total Sales Calculation Problem

awhitfield — Fri, 31 Jul 2015 09:11:26 GMT

Hi uday,

can you upload a sample qvw and source data please?

Andy

Re: Total Sales Calculation Problem

Kushal_Chawda — Fri, 31 Jul 2015 09:17:00 GMT

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))))

Re: Total Sales Calculation Problem

Gysbert_Wassenaar — Fri, 31 Jul 2015 09:17:15 GMT

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

Re: Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 09:26:16 GMT

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.

Re: Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 09:28:37 GMT

Hi Kush,

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

Re: Total Sales Calculation Problem

Gysbert_Wassenaar — Fri, 31 Jul 2015 09:36:52 GMT

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

Re: Total Sales Calculation Problem

Kushal_Chawda — Fri, 31 Jul 2015 09:40:57 GMT

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

or please share the screenshot of your expression

Re: Total Sales Calculation Problem

Fri, 31 Jul 2015 09:51:17 GMT

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..

Re: Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 09:56:03 GMT

It has returned NULL.

Re: Total Sales Calculation Problem

Gysbert_Wassenaar — Fri, 31 Jul 2015 09:57:56 GMT

Check the syntax of the expression. Make sure to use the exact case sensitive field names.

Re: Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 10:04:57 GMT

Hi Pratik,

It is working fine even in total mode when I set all the three expressions to "Expression Total" in total mode which is not the requirement.

Re: Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 10:06:51 GMT

I did Check the syntax multiple times and I even tried putting the entire expression in a variable and using it instead of field names. Nothing Worked!!

Re: Total Sales Calculation Problem

Gysbert_Wassenaar — Fri, 31 Jul 2015 10:22:06 GMT

Then either give up or post a qlikview document that demonstrates the problem so somebody else can have a look at it. As you've noticed letting us guess is wasting time for all of us.

Re: Total Sales Calculation Problem

PrashantSangle — Fri, 31 Jul 2015 10:29:44 GMT

Hi,

Try,

Column(2) / Column(1)

Considering Column(2) as ([Sales to date])

and Column(1) as [Billing Days to Date]

As Gysbert suggest post your app.

Re: Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 10:30:42 GMT

I'll try and create a sample document and post it.

Re: Total Sales Calculation Problem

PrashantSangle — Fri, 31 Jul 2015 10:33:47 GMT

Hi,

Try my suggestion.

Since you can not use Sum() on columnName therefore it is giving you error.

Regards

Re: Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 10:41:25 GMT

PFA Sample Data and QVW

Re: Total Sales Calculation Problem

Anonymous — Fri, 31 Jul 2015 11:11:35 GMT

Hi Andrew,

PFA Sample QVW and Data.

Re: Total Sales Calculation Problem

Gysbert_Wassenaar — Fri, 31 Jul 2015 12:54:13 GMT

Where's that final sales expression you mentioned in your opening post? I don't see that in the document you posted.