Default values if field is not selected in Qlikview

[dselgo_eidex]

Hello, I was wondering if there was any way that a field could be defaulted to a specific value within set analysis. For instance, say I have the following table representing the number of students in a school:

School	Grade	Ethnicity	Gender	Student Count
School A	5	All	All	50
School A	5	Hispanic	All	15
School A	5	Hispanic	Female	6
School A	5	Hispanic	Male	9
School A	6	All	All	55
School A	All	Hispanic	All	70
School A	All	All	Male	230

Basically, I'm getting student counts at 3 levels of detail: grade, ethnicity, and gender. And I'm getting summed up values for each level of detail as well. I have listboxes for grade, ethnicity, and gender. What I would like to do is set up the set analysis in my chart to find the student count based off of the current selections, but if no selection is given for any of the fields it uses 'All' in the set analysis.

I can do this with the GetSelectedCount() function like this:

if(GetSelectedCount([Grade]) = 0, sum({$<[Grade] = 'All'>} [Student Count], sum([Student Count])

but I would have to set up a separate nested if statements for each possible selection:

if(GetSelectedCount([Grade]) = 0 AND GetSelectedCount([Gender]) = 0 AND GetSelectedCount([Ethnicity]) = 0,

     sum({$<[Ethnicity] = {'All'}, [Grade] = {'All'}, [Gender] = {'All'}>} [Student Count]),

     if(GetSelectedCount([Grade]) <> 0 AND GetSelectedCount([Gender]) = 0 AND GetSelectedCount([Ethnicity]) = 0,

          sum({$<[Ethnicity] = {'All'}, [Gender] = {'All'}>} [Student Count]),

          if(GetSelectedCount([Grade]) = 0 AND GetSelectedCount([Gender]) <> 0 AND GetSelectedCount([Ethnicity]) = 0,

               sum({$<[Ethnicity] = {'All'}, [Grade] = {'All'}>} [Student Count]),

               if(GetSelectedCount([Grade]) = 0 AND GetSelectedCount([Gender]) = 0 AND GetSelectedCount([Ethnicity]) <> 0,

                    sum({$<[Gender] = {'All'}, [Grade] = {'All'}>} [Student Count]),

                    if(GetSelectedCount([Grade]) = 0 AND GetSelectedCount([Gender]) <> 0 AND GetSelectedCount([Ethnicity]) <> 0,                                     sum({$<[Grade] = {'All'}>} [Student Count]),

                         if(GetSelectedCount([Grade]) <> 0 AND GetSelectedCount([Gender]) = 0 AND GetSelectedCount([Ethnicity]) <> 0,                                      sum({$<[Gender] = {'All'}>} [Student Count]),

                              if(GetSelectedCount([Grade]) <> 0 AND GetSelectedCount([Gender]) <> 0 AND GetSelectedCount([Ethnicity]) = 0,                                     sum({$<[Ethnicity] = {'All'}>} [Student Count]),

                                   sum([Student Count]

                              )

                         )

                    )

               )

          )

     )

)

I would like to avoid this because (from my understanding) QlikView will calculate each of these sums before evaluating the conditional to return the right value, and this is just a bulky expression.

Is there a way that I can specify somewhere in QlikView that if a field has no selections on it to default to a given value? In this case, I would set all of these fields to default to 'All'.

[johnw]

If the "All" values are truly just the sum of the details, I would remove all such rows from my data model. Just keep the detail rows, and let QlikView do the summing.

[#3]

I think you can use triggers as soon as the sheet is activated. And make some default selections.

I agree with John, the data model should not contain aggregated data as part of detail data.

It could lead to incorrect summations and calculations and you will have to keep applying set qualifiers to exclude them from calculations going forward.

[dselgo_eidex]

I agree with that, but unfortunately the data sets that I work sometimes withhold that detailed data. In accordance with FERPA and to protect student's information, very small amounts (such as 3rd Grade, African American, Males) may be masked. The aggregations are given though so they provide a more accurate value than the sum of the detailed levels.

[dselgo_eidex]

It essentially becomes when I have aggregated data like this and need to maintain and comply with that aggregation, I have to create create a condition for each possible selection state. For each dimension there are two possibilities: a selection has been made = use selection and a selection has not been made = use all. That means that with the way I have it laid out now, I will have to manage 2^N conditions where N is the number of dimensions I'm working with. I'd rather not have an expression with 8 or 16 nested if statements.

Any other possible solutions for this? If there was a way that I could specify in the set analysis to do this, such as:

sum({$<

     [Ethnicity] = if(GetSelectedCount([Ethnicity]) = 0, {'All'}, $::[Ethnicity]),

     [Grade] = if(GetSelectedCount([Grade]) = 0, {'All'}, $::[Grade]),

     [Gender] = if(GetSelectedCount([Gender]) = 0, {'All'}, $::[Gender]),

>} [Student Count])

it would fix my problem, but as far as I know there is no way to set up a conditional in set analysis like this.

[Anonymous]

Best way is to remove all and calculate the missing detail columns as mentioned before.

But if you need something, I'm not 100% sure what you're doing but maybe you could use 3 variables?

let vGrade=if(GetSelectedCount(Grade)=0,'All',getfieldselections(Grade));

let vGender=if(GetSelectedCount(Gender)=0,'All',getfieldselections(Gender));

let vEthnicity=if(GetSelectedCount(Ethnicity)=0,'All',getfieldselections(Ethnicity));

Then make your expression something like:

sum({$<[Grade] = {'$(vGrade)'},Gender={'$(vGender)'},Ethnicity={'$(vEthnicity)'}>} [Student Count]

Haven't tested it for syntax or anything but I think the theory should work.

johnw‌ might be able to help with syntax.

[Kushal_Chawda · Accepted solution]

try like this

Create the variable on front end

let vGrade=if(GetSelectedCount(Grade)>0,concat(DISTINCT chr(39) &Grade &  chr(39),','),chr(39)&'All'&chr(39))

let vGender=if(GetSelectedCount(Gender)>0,concat(DISTINCT chr(39) &Gender &  chr(39),','),chr(39)&'All'&chr(39))

let vEthnicity=if(GetSelectedCount(Ethnicity)>0,concat(DISTINCT chr(39) &Ethnicity &  chr(39),','),chr(39)&'All'&chr(39))

Now your expression will be

=sum({<Ethnicity = {$(vEthnicity)},Gender={$(vGender)},Grade={$(vGrade)}>} [Student Count])

[dselgo_eidex]

Thanks Wallo for the suggestion. I did some testing and I think it should work. I marked Kushal's response as the correct answer because of the syntax. Thanks everyone!

[Anonymous]

No problem, Danny. dselgo_eidex‌

Sorry I didn't have time to test or hammer out syntax for you.  I was in a hurry.

I'm glad you found something that works.

Thanks for finishing it out kush141087‌

[Kushal_Chawda]

Hi dselgo_eidex,  You can mark atkinsow's answer as helpful as the method he suggested was correct.