topic Re: Expression Help in QlikView

Expression Help

Thu, 29 Oct 2015 08:42:59 GMT

HI All

I am currently using this set analysis for calculating Netsales in a Straight Table

SUM(

AGGR(

(

Sum({<MONTH={'$(vThismonth)'},CR -={1}>}NETSALE))

now i need to exclude 75% of credits, with flag 1

exclude CR = 1

how to achieve this

i tried this but result not as expected its generating all values as 0

SUM(

AGGR(

(

Sum({<MONTH={'$(vThismonth), 0.75 * CR -={1}'}>}NETSALE))

Please Suggest

Re: Expression Help

morganaaron — Thu, 29 Oct 2015 08:46:34 GMT

Exclude 75% of credits? Are those defined or do you just mean 75% of the value? If so, just calculate the NETSALE value for the credits separately, multiply that by 0.75 and subtract it from your initial expression..

Re: Expression Help

vishsaggi — Thu, 29 Oct 2015 08:48:48 GMT

Hello John,

Could you share a sample file and expected output so that we can work on it easily.

Thanks,
V.

Re: Expression Help

Thu, 29 Oct 2015 08:59:51 GMT

HI Aaron

here is the small data sample, please suggest how can achieve this in the script level only with CR = 1

Re: Expression Help

morganaaron — Thu, 29 Oct 2015 09:10:42 GMT

I don't know what dimensions your table is holding, as you don't seem to have included the field you're aggregating over in your first post, but the theory behind the actual sum (ignoring aggr for now) is just saying:

Sum({<MONTH={'$(vThismonth)'}>}NETSALE)) - (Sum({<MONTH={'$(vThismonth)'}, CR={1}>}NETSALE)) * 0.75)

Unless I've misunderstood you?

Re: Expression Help

HirisH_V7 — Thu, 29 Oct 2015 09:13:17 GMT

Hi ,

For example we can use like this,

=Sum({<CR={0}>}NETSALE)

=Sum({<CR={1}>}NETSALE)

=Sum({<CR-={0}>}NETSALE)

=Sum({<CR-={1}>}NETSALE)

But exactly explain whats your expression . By based on requirement we can use Precedes and Follows also.

Thanks,

Hirish

Re: Expression Help

Thu, 29 Oct 2015 09:51:34 GMT

HI Aaron

actually this is the complete Expression

SUM(

AGGR(

(

Sum({<MONTH={'$(vThismonth)'}, CR -={1}>}NETSALE)

Only({<MONTH={'$(vThismonth)'}>}WORKINGDAYS)

)

,BRANCH)

Re: Expression Help

Kushal_Chawda — Thu, 29 Oct 2015 09:56:31 GMT

try this

(SUM(AGGR((Sum({<MONTH={'$(vThismonth)'}, CC -={1}>}NETSALE)

/Only({<MONTH={'$(vThismonth)'}>}WORKINGDAYS) ) ,BRANCH))

(SUM(AGGR((Sum({<MONTH={'$(vThismonth)'}, CC -={1}>}NETSALE)

/Only({<MONTH={'$(vThismonth)'}>}WORKINGDAYS) ) ,BRANCH)*0.75)

Re: Expression Help

morganaaron — Thu, 29 Oct 2015 10:17:20 GMT

SUM(

AGGR(

(Sum({<MONTH={'$(vThismonth)'}>}NETSALE) - (Sum({<MONTH={'$(vThismonth)'}, CR={1}>}NETSALE) * 0.75))

/ Only({<MONTH={'$(vThismonth)'}>}WORKINGDAYS)

, BRANCH))

Re: Expression Help

Thu, 29 Oct 2015 10:38:38 GMT

Thanks Aaron

Thank You All