Exclude Null and Include Current Selections with Alternate States

[shyamcharan]

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.

[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]

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 · Accepted solution]

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

[shyamcharan]

Hi Sunny,

Thanks.

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

This bit of code fixed my problem.