.

Thu, 30 Jan 2014 06:24:08 GMT

=if((Sum({}[Sales Amount]) -Sum({}[Purchase Gross Amount])) =0, (( (Sum(TOTAL{}[Sales Amount]) - Sum(TOTAL{}[Purchase Gross Amount])) / (sum(TOTAL(Aggr(only({}[Per Day Count]),[Order Date])))) ) * (Sum(Aggr(Only({}CountWorkingDays),[Order Month]))) ), ((Sum({}[Sales Amount]) -Sum({}[Purchase Gross Amount])) )) This does not gives exact total. please advise.

Re: Subtotal not correct

Thu, 30 Jan 2014 06:36:05 GMT

Can you please break down this expression into different other expressions. Then you can analyze what is happening.

Like:

1) Sum({}[Sales Amount])

2) Sum({}[Purchase Gross Amount])

3) Sum(TOTAL{}[Sales Amount])

4) Sum(TOTAL{}[Purchase Gross Amount])

5) Aggr(only({}[Per Day Count]),[Order Date]) .. etc

or you can try :

1) (Sum({}[Sales Amount]) -Sum({}[Purchase Gross Amount]))

2) (Sum(TOTAL{}[Sales Amount]) - Sum(TOTAL{}[Purchase Gross Amount]))

3) (sum(TOTAL(Aggr(only({}[Per Day Count]),[Order Date])))) )

etc...

Thanks,

Angad

Re: Subtotal not correct

Thu, 30 Jan 2014 06:52:49 GMT

Can u post sample qvw.

You can try using sum(if).

HTH

-shruti

Re: Subtotal not correct

sunilkumarqv — Thu, 30 Jan 2014 09:11:27 GMT

Hi,

If you want to use multiple experssion in one definition you to break the columns list like this Please go fllowing as of suggested this by Karl Pover

Parallel coordinates chart with quantitative values

Important functions

The valuelist() function allows us to add a list of values that does not originate from the data model. For example,

ValueList(‘Net Sales’, ‘Profit Margin’, ‘Days Worked’, ‘Num. of Consultants’)

allows us to create a dimension in a chart with the four values that are included within the parenthesis. Although this function is very useful, we only use it when the dimension values cannot be add directly to the data model.

If we define our dimension as above then the next step is to define the expression that corresponds to each dimension value. We take advantage of the pick() and match() function to perform this trick. The following formula will define an expression for each dimension value.

pick(

match(ValueList(‘Net Sales’, ‘Profit Margin’, ‘Days Worked’, ‘Num. of Consultants’)

,‘Net Sales’, ‘Profit Margin’, ‘Days Worked’, ‘Num. of Consultants’)

, sum([Net Sales])/max(total aggr(sum([Net Sales]),Customer))

, sum([Profit Margin])/max(total aggr(sum([Profit Margin]),Customer))

, sum([Days Worked])/max(total aggr(sum([Days Worked]),Customer))

, count(distinct Employee)/max(total aggr(count(distinct Employee),Customer))

)

Creating the parallel coordinates chart in QlikView

If you want to follow along with the exercise below, download Sales_Project_Analysis_Sandbox.qvw.

Create a line chart with the following two dimensions:
- =ValueList(‘Net Sales’, ‘Profit Margin’, ‘Days Worked’, ‘Num. of Consultants’)
- Customer
Define the expression as follows:
- pick(
  match( ValueList(‘Net Sales’, ‘Profit Margin’, ‘Days Worked’, ‘Num. of Consultants’)
  , ‘Net Sales’, ‘Profit Margin’, ‘Days Worked’, ‘Num. of Consultants’
  )
  , sum([Net Sales])/max(total aggr(sum([Net Sales]),Customer))
  , sum([Profit Margin])/max(total aggr(sum([Profit Margin]),Customer))
  , sum([Days Worked])/max(total aggr(sum([Days Worked]),Customer))
  , count(distinct Employee)/max(total aggr(count(distinct Employee),Customer))
  )

Hope You understand this !

Regards

Sunil

topic . in QlikView

.

Re: Subtotal not correct

Re: Subtotal not correct

Re: Subtotal not correct