topic Re: Selection criteria sum in QlikView

Selection criteria sum

Karim_Khan — Thu, 20 Aug 2015 13:23:24 GMT

HI Team,

I have two list boxes [Sales Level 1] and [Sales level 2].I wanted to fetch the value on the basis of below logic

sum( [Sales level 2])/ sum([Sales level 1]).

But Sales Level 1 is a Parent like

AMERICAS

and Sales level 2 is child like CANADA,LATAM,US etc.

The Sales Level 1 and Sales Level 2 has the dynamic value.By applying below expression for [Sales level 2] calculation its not working dynamically.

=sum({$<[Sales Level 2]=>}[Net Bookings])

Re: Selection criteria sum

sunny_talwar — Thu, 20 Aug 2015 13:24:46 GMT

Not sure I completely understand, would you be able to share few lines of data with your expected output??

Re: Selection criteria sum

kkkumar82 — Thu, 20 Aug 2015 13:29:25 GMT

Hi,

I think you want to have aggregated value based on Sales Level1 ,so I suggest

aggr( sum({$<[Sales Level 2]=>}[Net Bookings]), [ Sales Level 1] )

Hope I got you.

Thanks

Kiran Kumar

Re: Selection criteria sum

Karim_Khan — Thu, 20 Aug 2015 14:01:29 GMT

Sir,

I have tried this expression

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

which is giving me the correct value for CANADA YTD.

and for AMERICAS

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

It is giving the total of all Sales Level 2 which comes under Amercias Sales Level 1. YTD

But whenever I deslect and select then the value gets blank for First expression

Pls suggest

Re: Selection criteria sum

Anonymous — Thu, 20 Aug 2015 14:08:19 GMT

Are you essentially looking for sales numbers that make up a region?

Try this:

Aggr(sum([Sales Level 2]/[Sales Level 1]), [Sales Level 1])