Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Limit dimension in table considering filters and variables

Hi,

in my application I have a Table. I want the Dimension of the table to consider the current selection and some variable values. The formula for the Dimension is as follows:

If( [Field1] > vAriable1 and [Field2] < vAriable2, [ID])

So this gives me all the [ID]'s as Dimension for the table I want, considering the current selection and variable values.

My Problem is, that the Table might contain up to 5Mio datasets. so that if the current selection and variable values are not wisely chosen the application keeps loading and loading and the RAM is overflowing.

To avoid that, I tried to use the getPossibleCount()-function to limit the amount of datasets to 100.000, however the function only considers the current selection and not the variable values:

IF(GetPossibleCount([ID])>100000,'To many datasets', If([Field1] > vAriable1 and [Field2] < vAriable2, ID))

Is there a way to Limit the amount of datasets for the table which considers the current selection and varaible values?

I thought of something like this:

If( Count( If(  [Field1] > vAriable1 and [Field2] < vAriable2, [ID])) > 100.000, 'Too many datasets', [ID])

Thanks in advance

17 Replies
Not applicable
Author

After first sight it indeed sounds pretty cool. This would probably take my app to a new level. However I am not sure yet if that will be necessary to meet  the user's needs. I will evaluate that. Thank you very much for sharing that subject.

Not applicable
Author

I guess I am missing an extension you used as I have two invalid visualizations (inputvariable) in your app.

I did not know that it is possible to give variables values using expressions somewhere else than in the Load Script. This of course offers new possibilities and is definitely good to know 😉

May I ask which extension that is or did you manage to do that in some other way?

If I can give a variable a value using an expression I think your solution will work.

Thanks a lot

OmarBenSalem

You can download the extension here:

GitHub - LorisLombardo87/sense-input-variable: Sense Input Varible

Once done, the app  will work just fine

Not applicable
Author

Thanks for the fast reply. Now the visualizations work. One last question: Where does the variable vTest get it's value ?I can not find it nowhere in the app 

OmarBenSalem

Capture.PNG

shubham_singh
Partner - Creator II
Partner - Creator II

please mark correct answer if your problem has been resolved

Not applicable
Author

Okay, I wanted to calculate the maximum amount of datasets using if conditions but it seems to only work with Set Analysis. I guess this could take some time for me as I have not used Set Analysis yet, but it should be possible.

But if someone is an expert, please do not bother to tell me how this expression would look using Set Analysis 😉

Maybe also with using count(aggr(only({}))) like Shubham proposed.

Count(If(

$(=v1) and

$(=v2) and

[Field1] <= $(=v3) and

[Field2] <= $(=v4) and [Field2] >= $(=v5)

,ID))

v1 and v2 are boolean

v3, v4 and v5 are numbers

Not applicable
Author

Okay,after some hours today, it works. For the boolean variables I created Flag-Fields. What took the most time, was to find out that you have to put an = sign at the start of the variable Expression in the variable editor.

You defined your vTest as follows:

=count(aggr(only({<Sales={">=$(x)"},Revenue={">$(y)"}>}[Customer Name]),[Customer Name]))


Is there a reason why you used this expression instead of this:

Count({<Sales={">=$(x)"},Revenue={">$(y)"}>}[Customer Name])

?

I would be delighted if I still got a reply.