topic Re: Sum if formula in function in QlikView

Sum if formula in function

ranibosch — Tue, 24 Oct 2017 06:45:47 GMT

Hi, my table looks as follows:

I would like my object to calculate the number of units sold.

Basically my calculation should work as follows:

For each ITEM NO do the following:

sum (Invoice Quantity)

------------------------------------- and then sum this answer for all items in the end.

packsize UOM

how do i write this on my object to calculate like this???

Re: Sum if formula in function

pradosh_thakur — Tue, 24 Oct 2017 06:55:37 GMT

1:Use a straight table

2: dimesnion : [Item no]

3: expression sum([Invoice qty])/[Packsize UOM]

4:show expression total

regards

Pradosh

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 07:03:19 GMT

Hi Pradosh,

I want to use a chart as I'm doing sales trending.. ZAR values vs Total Tonnage vs Total Units

See screenshot of my charts. I'm basically looking to create the 3rd total units chart.

Current formula in these charts are basically:

sum( { < [Trending Value] = {"MP"} > }[Invoiced Quantity])

Re: Sum if formula in function

pradosh_thakur — Tue, 24 Oct 2017 07:17:36 GMT

May be this

sum(aggr(sum([Invoice qty])/[Packsize UOM],[Item no]))

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 07:21:28 GMT

How do I add my set analysis in this for the MACHINE PACK, HAND PACK etc??

currently the formula looks as follows:

sum( { < [Trending Value] = {"MP"} > }[Invoiced Quantity])

Re: Sum if formula in function

prma7799 — Tue, 24 Oct 2017 07:25:59 GMT

try like this

sum( { < [Trending Value] = {'MP','HP'} > }[Invoiced Quantity])

sum( { < [Trending Value] = {"MP"} , YourField = {"HP"} > }[Invoiced Quantity])

Re: Sum if formula in function

pradosh_thakur — Tue, 24 Oct 2017 07:45:13 GMT

Hi Rani

i guess above is the solution by MP but you should consider reading this once as it may appear as a bug in future

Quotes in Set Analysis

double quotes are case insensitive so make sure what you use is right for your requirement?

did we answer your question.

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 07:56:53 GMT

Hi pradosh,

I'm really new a qlikview and need you guys to dumb it down for me..

I need my chart to do the above calculation for me, and then furthermore need to do it for machine packed vs hand packed items.

the packing method is item no specific.

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 07:58:57 GMT

I've tried the following code, but it is not working:

sum({<[Trending Code] = {"MP"} > }(aggr(sum([Invoiced Quantity])/[Packsize UOM],[Item no])))

Re: Sum if formula in function

prma7799 — Tue, 24 Oct 2017 08:01:29 GMT

Try like this

sum((aggr(sum({<[Trending Code] = {"MP"} > }[Invoiced Quantity])/[Packsize UOM],[Item no])))

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 08:08:17 GMT

not working..

sum ( ( aggr ( sum ({ < [Trending Value] = {"MP"} > } [Invoiced Quantity]) / [Packsize UOM] , [Item no] ) ) )

Re: Sum if formula in function

pradosh_thakur — Tue, 24 Oct 2017 08:34:00 GMT

what is the field that contains Hand acked and Machine Packed?

will you be able to share a sample app?

if your data is confidential then use the below procedure .

Preparing examples for Upload - Reduction and Data Scrambling

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 08:52:00 GMT

Unfortunately I will not be able to share a model..

See my current table below with highlighted fields:

FIELD NAME VALUE IN FIELD COMMENT

1. Item No = Item 1; Item 2; Item 3 List of item number

2. Invoice quantity = 300; 400; 500 Quantities sold per transaction

3. Trending value = MP; HP Machine or hand packed

4. Packsize UOM = 10, 20, 50 Item specific pack size, not all items are the same

So my graph needs 2 lines, 1 for machine pack and 1 for hand packing in order to trend my units sold.

Total units is calculated by looking at each item individually (as items do not all have the same unit weight). This can be done in math terms as follows:

a. sum [Invoice quantity] of each item;

b. then divide a by each items' Packsize UOM;

c. then sum the total of a & b.

Re: Sum if formula in function

prma7799 — Tue, 24 Oct 2017 09:04:06 GMT

Can you please upload some sample / scramble data?

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 09:42:44 GMT

PLEASE FIND ATTACHED.

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 09:51:18 GMT

DO YOU THINK PERHAPS I SHOULD CREATE A NEW FIELD ON MY ValueEntries table CALLED UNITS SOLD?

should i rather create a units field in my script. would that simplify things?

Re: Sum if formula in function

prma7799 — Tue, 24 Oct 2017 10:05:08 GMT

Hi Rani,

Please share some expected output...

Thanks

Re: Sum if formula in function

ranibosch — Tue, 24 Oct 2017 11:19:02 GMT

attached i've included data and expected output..