topic Re: calculated feild in Connectivity & Data Prep

calculated feild

Wed, 27 Sep 2017 11:00:42 GMT

Hi,

I am trying to import data from a csv file which have is similar to following table:

This file already contains first five columns of table shown above. I need a calculated field "Sales Amount" which should be calculated like :

Sales Amount = (Qty x Price ) + Postage

But Problem i was facing is that for orders in which there are multiple skus/items for example order id 1002, in which there are two skus/items (i.e S001, S002). Even though customer has paid only $6 Postage it's showing against both items which results into inflating the total "Sales Amount". In excel we can use following formula to resolve the issue and divide the postage equally to all skus/items :

=(C2*D2)+(E2/COUNTIF(A:A,A2))

I am wondering how i can achieve the same in Qlikview dataload editor script, Appreciate if someone can help/guide me.

Thanks

Re: calculated feild

sunny_talwar — Wed, 27 Sep 2017 12:01:33 GMT

May be this

Sum(Qty * Price) + (Postage/Count(DISTINCT TOTAL <OrderID> SKU))

Re: calculated feild

Wed, 27 Sep 2017 13:26:35 GMT

hi,

Thanks for the quick reply do i have to use it in dataload script or create a Master measure using this formula ? i am asking because i tried putting this in dataload script but it does not work, i tried creating master measure using following :

Sum([Quantity] * [Product Price]) + ([Carriage Amount]/Count(DISTINCT TOTAL <[Order ID]> [SKU]))

it works perfectly for data tables but does not work in visualizations(charts) of any kind.

Can you please guide me what I am doing wrong ?

Thanks

Re: calculated feild

sunny_talwar — Wed, 27 Sep 2017 15:50:27 GMT

What was your dimension in the chart?

Re: calculated feild

Thu, 28 Sep 2017 09:56:29 GMT

Its Dates, (Months & Weeks) mostly.

Re: calculated feild

sunny_talwar — Thu, 28 Sep 2017 13:34:01 GMT

But your sample data doesn't show any date... I am confused... where did date come in from?

Re: calculated feild

Wed, 04 Oct 2017 12:42:43 GMT

Original data consists more than 50 fields, for the sake of simplicity i only used few fields in sample data which i thought would be relevant.

Re: calculated feild

Wed, 04 Oct 2017 12:45:24 GMT

And can you advise how can we do the same in loadscript.

Re: calculated feild

sunny_talwar — Wed, 04 Oct 2017 12:52:52 GMT

I guess I understand that, but the fields which you use in your dimension/expression, I would expect those to show up in the sample data... otherwise what kind of a sample would it be

Re: calculated feild

Thu, 05 Oct 2017 11:01:57 GMT

As requested here is the revised sample data with few of the dimensions:

Re: calculated feild

sunny_talwar — Thu, 05 Oct 2017 11:13:50 GMT

I apologize for the inconvenience, but would you be able to share this as an Excel file?

Uploading a Sample

Re: calculated feild

Wed, 18 Oct 2017 18:02:05 GMT

hi,

i have almost resolved the problem, need one little help can you guide my how can i convert this "measure" calculation code to data load editor code :

([Carriage Amount]/Count(DISTINCT TOTAL <[Order ID]> [SKU]))

so I can load it as calculated field instead of a measure.