topic Re: How to show the sub values along with Total value in QlikView

How to show the sub values along with Total value

Karim_Khan — Mon, 14 Sep 2015 13:54:02 GMT

Hi Team,

I am trying to get the % of Bookings on the selection of values.when I select the [Sales Level 1] and[ Sales Level 2]

then it should show the [Sales Level 2] Data values.

Suppose [Sales Level 1] we select AMERICAS then it show the all dependent value of [Sales Level 2] like

CANADA,LATAM,US COMMERCIAL,US OTHER etc.

I am able to achieve the first two Columns value i.e Count and Bookings but while am trying to get the % of booking it showing the wrong calculation.

PFA

Regards,

MRKachhiaIMP‌jagan‌gwassenaar‌ashfaq_haseeb‌Kush141087‌

Re: How to show the sub values along with Total value

MK_QSL — Mon, 14 Sep 2015 14:19:33 GMT

Use

SUM(YourExpression) / SUM(TOTAL YourExpression)

Re: How to show the sub values along with Total value

Karim_Khan — Mon, 14 Sep 2015 14:26:38 GMT

Its not working sir

=sum({$<[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 1]=, [Sales Level 2]={$(vSel2)}>} [Net Bookings])/sum(TOTAL({$<[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 1]=, [Sales Level 2]={$(vSel2)}>} [Net Bookings]))

Re: How to show the sub values along with Total value

MK_QSL — Mon, 14 Sep 2015 14:36:04 GMT

Can you provide expected result in excel file?

Re: How to show the sub values along with Total value

Karim_Khan — Mon, 14 Sep 2015 14:48:47 GMT

Re: How to show the sub values along with Total value

MK_QSL — Tue, 15 Sep 2015 08:36:59 GMT

% Bookings

num(sum({$<[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 2]=>} [Net Bookings]),'##.00')

num(sum(TOTAL {$<[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 2]=>} [Net Bookings]),'##.00')

Last Column

num(sum({$<[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 2]=>} [Net Bookings]),'##.00')

count({$<[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 2]=>}DISTINCT([End Customer Global Ultimate Name]))

Re: How to show the sub values along with Total value

Karim_Khan — Tue, 15 Sep 2015 10:08:12 GMT

Thanks sir