Is there a way to create a subset of a dataset to be displayed in a chart using the expression field?

[#1]

I have loaded up 1.5 years of data into qlikview. 3 charts will show up the 1.5 year data and it is showing up correctly.

However for a particular chart, I only want to display the current year data, totalling up only the volume values(excluding revenue).

Is it possible to segment this data set using expression?

I did something like this - sum(if(NUM(Left(Period,4))=$(vCurrentRptYear),Sum({$<[Measure]={'[Sale Volume (Actual)]'}>}[Value])))

But it didnt work - nothing showed up.

Anyone has any ideas?

[lironbaram]

hei

can you be more specific did you try to use a calculated measure in your table or

measure is a field

can you post some sample data or the table fields

[kaushiknsolanki]

Hi,

    You can right the set analysis to achieve this.

    The expression will be.

    Sum({<Year = {"$(vCurrentRptYear)",Measure = {"

Sale Volume (Actual)"} }>}Value)

    Hope this will solve your problem.

Regards,

Kaushik Solanki 

[#4]

You can try with an expression like

sum({$<Year={2000}, Region={“U*”}>} Sales) which uses 2 conditions. You cannot use an expression NUM(Left(Period,4)) in the left side.

You have to create an year field with this expression first and then use like Year = {$(vCurrentRptYear)}

[#5]

Under chart properties, under Expression-> Definition.

I created an additional field _Year in the data set and put in this expression

sum({<_YEAR={$(CurrentRptYear)}>} [VALUE]) - just to test it out with only one criteria but nothing showed up in the chart.

[#6]

try with the below expression please.

sum({<_YEAR={'$(CurrentRptYear)'}>} [VALUE])

[#7]

The data did show up without using 'v'. But there was a problem - the filter which I created with this expression seems to be superceded by the list box which is selected.

Example list box I select Period - Jun 2010 to Jun 2011.
But within the chart itself, 2010 data has also been included. Any idea?

[#8]

you have to use 1 in the expression to have a subset excluding the current selection. Then your expression will be

sum({1<_YEAR={'$(CurrentRptYear)'}>} [VALUE])

[#9]

It was very helpful, thanks!

Is there a way to include this subset with a partial selection from the list box?

[#10]

I found a way to use a list box selection. By creating a variable and set it as follows - =GetFieldName(SelectedFieldname)

And use this in the expression box as seen below- but no charts appear. Is there a problem with this logic/syntax?

Thanks!

   Sum({1<[_Year] = {$(CurrentRptYear)},[Measure] = {'Sale Volume (Actual)'},[Brand] = {$(BrandListBoxSelection)}>} [Value])