topic Re: Expression to calculate net sold quantity in QlikView

Expression to calculate net sold quantity

sakshikaul — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a querry regarding creating a sell through report. Following is the expression which shows the comparison between two seasons

=sum({stateperA< [Season Code]={'17A'} >} quantity_sold)

- sum({stateperA< [Season Code]={'17A'} >} quantity_returns)

Following above code is giving correct result but I want the user should have a freedom to select any season code so how to write an expression for the same ?

Re: Expression to calculate net sold quantity

ogautier62 — Fri, 24 Aug 2018 07:29:33 GMT

Hi,

remove set analysis and let user choose with a list box selection ?

it's native functionality in qlik

regards

Re: Expression to calculate net sold quantity

sakshikaul — Fri, 24 Aug 2018 08:59:55 GMT

I am getting exactly same figures for both the seasons.I have used following expressions

Qty recvd(LY)

=sum({stateperA} quantity_received)

Qty Recvd(CY)

=sum({stateperfB} quantity_received)

Re: Expression to calculate net sold quantity

ogautier62 — Fri, 24 Aug 2018 11:10:36 GMT

I don't understand very well

could you share a sample and expected result,

and what are the states stateperA and stateperB

thank you

Re: Expression to calculate net sold quantity

manoranjan_d — Fri, 24 Aug 2018 11:30:05 GMT

go with dynamic selection set analysis in the expression

example

=sum({<B1=$::B1>}C1)

=sum({<B1=p(B1)>}C1)

Re: Expression to calculate net sold quantity

BalaBhaskar_Qlik — Fri, 24 Aug 2018 13:15:52 GMT

Create a List box of [Season Code], then the values selected from this field, passed to Set Analysis.

=sum({stateperA< [Season Code] = {'$(=getfieldselections([Season Code]))'} >} quantity_sold)

- sum({stateperA< [Season Code] = {'$(=getfieldselections([Season Code]))'} >} quantity_returns)

Re: Expression to calculate net sold quantity

sakshikaul — Mon, 27 Aug 2018 05:00:16 GMT

This is not giving any result

Re: Expression to calculate net sold quantity

sakshikaul — Mon, 27 Aug 2018 05:52:39 GMT

This is not giving result. I am getting values as 0

Re: Expression to calculate net sold quantity

Anonymous — Mon, 27 Aug 2018 06:39:54 GMT

Hi,

Create Variable create input box .Ask User To enter the input of respective season code.

use that variable in set expression and write the formula as below.

Thanks

Ritesh

Re: Expression to calculate net sold quantity

raman_rastogi — Mon, 27 Aug 2018 07:16:12 GMT

Hi,

May be like this

Remove set analysis and use expression

=sum(quantity_sold) - sum( quantity_returns)

Create one list box of season code and Check list box property Always one value selected.

Re: Expression to calculate net sold quantity

Anil_Babu_Samineni — Mon, 27 Aug 2018 07:31:17 GMT

May be use like Implicit Set Operators‌

sum({stateperA< [Season Code]/={'17A'} >} (quantity_sold-quantity_returns))

Re: Expression to calculate net sold quantity

sakshikaul — Mon, 27 Aug 2018 07:37:38 GMT

But I have to show comparison between two seasons so how is this possible without set analysis ?

Re: Expression to calculate net sold quantity

sakshikaul — Mon, 27 Aug 2018 07:38:07 GMT

This is giving an error

Re: Expression to calculate net sold quantity

sakshikaul — Mon, 27 Aug 2018 08:43:11 GMT

yes i can do that but I have to also show comparison between two year ie yaer 2017 and 2018 so in that case this above idea is not useful

Re: Expression to calculate net sold quantity

sakshikaul — Mon, 27 Aug 2018 08:45:41 GMT

according to me i can get solution from this expression but this expression is not giving any result its giving 0 as value so please help what could be done?

Re: Expression to calculate net sold quantity

sakshikaul — Wed, 29 Aug 2018 10:17:59 GMT

Here is my expression for sold (QTY)(CY). The following expression is not giving result

=sum( {stateperfB< Sold_Date = {">=$(From)<=$(To)"},[Season Code] = {$(Master_CurrentSeason)} >} quantity_sold)- sum( {stateperfB< Sold_Date = {">=$(From)<=$(To)"}, [Season Code] = {$(Master_CurrentSeason)} >} quantity_returns)

Re: Expression to calculate net sold quantity

Anonymous — Wed, 29 Aug 2018 10:28:27 GMT

Hi,

if Season Code is having space in it so you have to mention the Season Code Field in single quotes

eg['Season Code'].

Regards

Ritesh

Re: Expression to calculate net sold quantity

BalaBhaskar_Qlik — Wed, 29 Aug 2018 10:38:11 GMT

May be use this:

=sum({<stateperA=p(stateperA), [Season Code] = {"$(=getfieldselections([Season Code]))"} >} quantity_sold)

sum({<stateperA=p(stateperA), [Season Code] = {"$(=getfieldselections([Season Code]))"} >} quantity_returns)