topic Re: How do I structure my set analysis to get next item sum in QlikView

How do I structure my set analysis to get next item sum

gerrycastellino — Fri, 20 Nov 2015 20:26:28 GMT

Re: How do I structure my set analysis to get next item sum

sunny_talwar — Fri, 20 Nov 2015 20:28:39 GMT

May be this?

=sum({<Item = {"$(=Item+1)" } >} Sales)

Re: How do I structure my set analysis to get next item sum

effinty2112 — Fri, 20 Nov 2015 20:38:04 GMT

Try:

=sum({$<Item = {$(=FieldValue('Item', FieldIndex('Item',Only(Item))+1))}>}Sales)

If you pick a single value of Item this expression will return the Sales value of the next Item (in load order) from that selected. So you can have Item values like 1, 5,678,'Jabba the Hut' in your list box.

Re: How do I structure my set analysis to get next item sum

gerrycastellino — Fri, 20 Nov 2015 20:47:57 GMT

a follow up question: I have a field as dual as below:

dual('2015-Q4',1) as Survey_Type,

I'm running into issues with this in my set:

Count({ < Survey_Type = {"$(=num(Survey_Type)+1)"} >}[Overall Sat])

Re: How do I structure my set analysis to get next item sum

sunny_talwar — Fri, 20 Nov 2015 20:53:04 GMT

What if you create another field like this:

LOAD Num(Survey_Type) as Num_Survey_Type;

LOAD dual('2015-Q4',1) as Survey_Type,

FROM....

Count({<Num_Survey_Type = {"$(=Num_Survey_Type + 1)"}>}[Overall Sat])

Re: How do I structure my set analysis to get next item sum

effinty2112 — Fri, 20 Nov 2015 20:59:16 GMT

Try:

=sum({$<Survey_Type= {$(=FieldValue('Survey_Type', FieldIndex('Survey_Type',Only(Survey_Type))+1))}>}[Overall Sat])

If the values of Survey_Type are loaded in the correct order this may work for you.

Re: How do I structure my set analysis to get next item sum

gerrycastellino — Fri, 20 Nov 2015 21:19:23 GMT

That worked, but I need to display the 2015-Q4 in my list box as selector.

Re: How do I structure my set analysis to get next item sum

gerrycastellino — Fri, 20 Nov 2015 22:01:34 GMT

will use a pick(match)