topic Re: need formula for tricky output in QlikView

need formula for tricky output

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

Dear All,

Need some help Please find the attachment with expected output.

Thanks

Re: need formula for tricky output

PrashantSangle — Thu, 09 Jun 2016 06:07:14 GMT

Hi,

What is your expression for contr???

try below logic

sum(ContriExpression)/Sum(Total <ST> ContriExpression) * 100

Regards

Re: need formula for tricky output

Anonymous — Thu, 09 Jun 2016 06:09:26 GMT

I used (SUM(Sale)/ SUM(TOTAL(Sale)))*100

But using above I am not get expected output

Re: need formula for tricky output

kamal_sanguri — Thu, 09 Jun 2016 06:14:17 GMT

use below expression:

(sum(Sale)/sum(total <ST>Sale))*100

Re: need formula for tricky output

kamal_sanguri — Thu, 09 Jun 2016 06:14:57 GMT

here is the updated qvw

Re: need formula for tricky output

PrashantSangle — Thu, 09 Jun 2016 06:16:13 GMT

Hi,

try like

(SUM(Sale)/ SUM(TOTAL<ST> Sale))*100

Regards

Re: need formula for tricky output

kamal_sanguri — Thu, 09 Jun 2016 06:17:11 GMT

or you can use :

(sum(Sale)/aggr(nodistinct sum(Sale),ST))*100

Re: need formula for tricky output

Anonymous — Thu, 09 Jun 2016 06:23:54 GMT

Hi Kamal,

in the attachment for ST contribution is missing (71.1 and 28.9)

Please see ex op excel.

Thanks

Re: need formula for tricky output

settu_periasamy — Thu, 09 Jun 2016 06:32:16 GMT

Try this ..

=if(Dimensionality()<=3,SUM(Sale)/ SUM(TOTAL(Sale))*100,
sum(Sale)/sum(total <ST>Sale)*100)

Re: need formula for tricky output

kamal_sanguri — Thu, 09 Jun 2016 06:32:24 GMT

what you want to see is the percentage distribution and the sum of percentage distribution will be equal to hundred.

for ex:

Item Share %dist

a 30 30%

b 40 40%

c 30 30%

subtotal - 100%

Re: need formula for tricky output

kamal_sanguri — Thu, 09 Jun 2016 06:51:19 GMT

here is the updated one..

I used the expression suggested by settu_periasamy‌..

Re: need formula for tricky output

Anonymous — Thu, 09 Jun 2016 07:27:57 GMT

Hi Settu, settu_periasamy‌

Your solution is right but when I am using this into main application then it is not showing correct values.

Please see below

Dim used :

1)Division

2)Product

3)State

4)Customer

Expression:

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Sales Return Amount]))/100000

Contr: ?

Re: need formula for tricky output

settu_periasamy — Thu, 09 Jun 2016 07:37:59 GMT

Maybe try this..

if(Dimensionality()<=3,

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount]-[Sales Return Amount])/

Sum(TOTAL {$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount]-[Sales Return Amount])),

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount]-[Sales Return Amount])/

Sum(TOTAL <State> {$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount]-[Sales Return Amount])))/100000

Re: need formula for tricky output

Anonymous — Thu, 09 Jun 2016 08:47:28 GMT

Hi settu_periasamy‌,

I tried but not get exp output..

Re: need formula for tricky output

settu_periasamy — Thu, 09 Jun 2016 08:49:43 GMT

Have you tried to separate the expression and see the result is correct or not?

Re: need formula for tricky output

Anonymous — Thu, 09 Jun 2016 09:00:17 GMT

Yes

Re: need formula for tricky output

settu_periasamy — Thu, 09 Jun 2016 09:35:24 GMT

Hi,

Could you prepare the sample with your issue illustrate?

I believe you are using that expression in pivot table.

May be try this..

if(Dimensionality()<=3,

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

- Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>}[Sales Return Amount]))/

(Sum(TOTAL {$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

-Sum(TOTAL {$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Sales Return Amount]),

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

- Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>}[Sales Return Amount]))/

(Sum(TOTAL <State>{$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

-Sum(TOTAL <State>{$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Sales Return Amount])))