Options to make visualizations load fast

osse_aam — Fri, 23 Jul 2021 19:28:13 GMT

Hello there!

I am using the following expression as a measure while designing a bar graph.

The result is as expected but the visualization is taking some time to load up on selections(understandable as there are many conditions in the expression). Is there any other simple/effective way to achieve this so that the performance wouldn't be low.

My expression:

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Students with Disabilities',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},[SWD Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},[SWD Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='At-Risk',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},[At-Risk]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},[At-Risk]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='English Learner',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},[English Learner Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},[English Learner Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='CFSA',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},CFSA={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},CFSA={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Homeless',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},[Homeless Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},[Homeless Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Military Connected',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},[Military Connected Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},[Military Connected Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Migrant Status',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},[Migrant Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},[Migrant Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Gender(Male)',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},Gender={'Male'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},Gender={'Male'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Gender(Female)',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},Gender={'Female'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},Gender={'Female'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])=0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Gender(Non-binary)',
if(Count({<[Type]={'Current'},[School Year]={'2020-2021'},Gender={'Non-binary'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[School Year]={'2020-2021'},Gender={'Non-binary'}>} distinct USI)
/Count({<[Type]={'Current'},[School Year]={'2020-2021'}>} distinct USI)),

if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Students with Disabilities',
if(Count({<[Type]={'Current'},[SWD Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[SWD Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='At-Risk',
if(Count({<[Type]={'Current'},[At-Risk]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[At-Risk]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='English Learner',
if(Count({<[Type]={'Current'},[English Learner Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[English Learner Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='CFSA',
if(Count({<[Type]={'Current'},[CFSA]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[CFSA]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Homeless',
if(Count({<[Type]={'Current'},[Homeless Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[Homeless Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Military Connected',
if(Count({<[Type]={'Current'},[Military Connected Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[Military Connected Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Migrant Status',
if(Count({<[Type]={'Current'},[Migrant Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},[Migrant Status]={'YES'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Gender(Male)',
if(Count({<[Type]={'Current'},Gender={'Male'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},Gender={'Male'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Gender(Female)',
if(Count({<[Type]={'Current'},Gender={'Female'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},Gender={'Female'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)),
if(getselectedcount([School Year])<>0 and valuelist('At-Risk','CFSA','English Learner','Homeless','Migrant Status','Military Connected','Students with Disabilities'
,'Gender(Male)','Gender(Female)','Gender(Non-binary)')
='Gender(Non-binary)',
if(Count({<[Type]={'Current'},Gender={'Non-binary'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'},Gender={'Non-binary'}>} distinct USI)
/Count({<[Type]={'Current'}>} distinct USI))

))))))))))
))))))))))

Thanks,

Sai.

Re: Options to make visualizations load fast

rwunderlich — Fri, 23 Jul 2021 20:52:53 GMT

I think your general approach is unwieldy. Here's what I would change.

1. Don't use ValueList.

2. Create a new field "Category" and populate with the ValueList items ('At-Risk', 'CFSA', etc) for each USI.

Then your chart becomes:

Dimension: Category

Measure:
if(Count({<[Type]={'Current'}>} distinct USI)
/Count({<[Type]={'Current'}>} TOTAL distinct USI)<0.001,'<0.1%',
Count({<[Type]={'Current'}>} distinct USI)
/Count({<[Type]={'Current'}>} TOTAL distinct USI))

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Options to make visualizations load fast

osse_aam — Sat, 24 Jul 2021 15:12:31 GMT

@rwunderlich - Thanks for the suggestion!

Currently, all the items inside valuelist are individual fields from the backend.

Will try adding a custom field like you mentioned or use a crosstable to flip the dataset and let you know the output if there is no possible way to implement this on the interface/visualization level itself.

Thanks,

Sai.

topic Re: Options to make visualizations load fast in App Development

Options to make visualizations load fast

Re: Options to make visualizations load fast

Re: Options to make visualizations load fast