topic Re: Exclude Null and Include Current Selections with Alternate States in QlikView

Exclude Null and Include Current Selections with Alternate States

shyamcharan — Mon, 13 Jun 2016 12:03:05 GMT

Hi Experts,

I do have a requirement to display total count in a text object.

I do have two alternate state filters to allow users see the variance between two state selections.

However, trying to allow users to see the difference between two states and also Include current selections on other default filters.

Also, the total should not include the Nulls or Error records for the Year.

Please find the attached qvw.

How can let the chart show only the non null values and allow the default state filter selections.

I will have to display total difference and the percentage as in the chart.

Thanks in advance.

Shyam.

Re: Exclude Null and Include Current Selections with Alternate States

sunny_talwar — Mon, 13 Jun 2016 12:06:14 GMT

There are 4 text boxes in your attached qvw file. Which one are we looking at? Also what is the expected output?

Re: Exclude Null and Include Current Selections with Alternate States

shyamcharan — Mon, 13 Jun 2016 23:06:46 GMT

Hi Sunny,

Sorry for the confusion. I have removed the other text boxes and kept the ones that needed, i.e. on the charts.

The requirement is to exclude nulls and allow current selections. (Both, In the chart and in the Text box below)

In the text box(in bold, below the chart) I need to show the difference between two state s1 & s2 selections. At the same I want to remove nulls in the calculations and allow the current selections on the fields like Priority.

In the chart, I need to remove the nulls and allow the current selections.

Please find the updated qvw attached.

Thanks in advance.

Re: Exclude Null and Include Current Selections with Alternate States

sunny_talwar — Tue, 14 Jun 2016 00:11:49 GMT

Can you check if the attach does it?

Script changes:

Variance:

LOAD RowNo() as Key,

Year&Quartr&Month&Count&Priority,

If(Len(Trim(Year)) = 0 or Len(Trim(Quartr)) = 0 or Len(Trim(Month)) = 0 or Len(Trim(Count)) = 0 or Len(Trim(Priority)) = 0, 1, 0) as Null;

LOAD * INLINE [

Year, Quartr, Month, Count, Priority

2012, 1, 1, 43, P1

2012, 1, 2, 545, P2

2012, 1, 3, 2345, P3

Text box object's expression:

=(Sum({<Year={">=$(=vs2Min)<=$(=vs2Max)"},Quartr={">=$(=vs2qtrMin)<=$(=vs2qtrMax)"}, Null = {0}, Priority = $::Priority>}Count)-

Sum({<Year={">=$(=vSetMinTest)<=$(=vSetMaxTest)"},Quartr={">=$(=vqtrMinTest)<=$(=vqtrMaxTest)"}, Null = {0}, Priority = $::Priority>}Count))

Chart's expression:

Pick(Match(ValueList('s1','s2'),'s1','s2'),

Sum({<Year={">=$(=vSetMinTest)<=$(=vSetMaxTest)"},Quartr={">=$(=vqtrMinTest)<=$(=vqtrMaxTest)"}, Null = {0}, Priority = $::Priority>}Count),

Sum({<Year={">=$(=vs2Min)<=$(=vs2Max)"},Quartr={">=$(=vs2qtrMin)<=$(=vs2qtrMax)"}, Null = {0}, Priority = $::Priority>}Count))

Re: Exclude Null and Include Current Selections with Alternate States

shyamcharan — Tue, 14 Jun 2016 06:05:00 GMT

Hi Sunny,

Thanks.

Null = {0}, Priority = $::Priority

This bit of code fixed my problem.