Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shyamcharan
Creator III
Creator III

Exclude Null and Include Current Selections with Alternate States

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.

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

4 Replies
sunny_talwar

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

shyamcharan
Creator III
Creator III
Author

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.

sunny_talwar

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))

View solution in original post

shyamcharan
Creator III
Creator III
Author

Hi Sunny,

Thanks.

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

This bit of code fixed my problem.