Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis depending of a selected field in a tree view list box

Hi,

on a my report I have created a tree view list box to show year and month dimensions. For it, I have created a specific year_month field by joining year and month. In this way, an user can select year = 2010 + month = 9 and year = 2011 + month = 3 and so on.

Then I have created two pivot chart, the first one with some measures with yeartomonth data for each month, and the second one with monthly measures. In other terms, in the firts pivot chart if I show measures related to year = 2011 and month = 3 it is as I show the measure values between january and march 2001, while measures in the second pivot are really monthly amounts. My source db is made in this manner.

So, when I make one or more selections on the tree view list box in the second pivot chart I must show the measures respect to the selected year and with month minus or equals to the selected month. I need to find the right set analysis expression. It must be similar to this:

sum({$<Month = {"<=$(varSelectedMonth)"}>} MeasureAmount)

but I add the part related to the year.

This expression must function when I select year = 2010 + month = 10 and year = 2011 + month = 3. For these selections, I must show 2010-01, 2010-02, 2010-03, 2010-04, 2010-05, 2010-06, 2010-07, 2010-08, 2010-09, 2010-10 and 2011-01, 2011-02, 2011-03.

Moreover, I have the problem to extract years and months selected from the tree view list box and put the right values on a variable to pass to the set analysis expression. I can select year = 2010 + month = 8 and year = 2011 + month = 3 and year = 2011 + month = 5 and so on; in other terms, I can made a multiple year + month selection.

Any suggests to me, please? Thanks



18 Replies
ToniKautto
Employee
Employee

I agree that synthetic keys by default would not give you problems, but they are very likely to cause trouble as you will not be able to control what data you are actually working with in your objects and expressions.

There should be no problem to create a set expression to cover a span of months. Generally it is a efficient idea to have a master calender that you link your dates to, this way you keep the date only in your other tables and then have fields in the master calender table to cover the convertion to Year / Month / Week / YearMonth / Day or what ever filtering capabilities you need.

Still, I think it is no time loss for you to eliminate the syn keys and get a proper data modell before you continue working with the docuemtn objects. If you finish the docuemtn and find out the results are incorrect hen you will need to redo everything. By securing your datat model first, you will not have to spend additional time on that again.



Not applicable
Author

Ok, Toni, but the focus of this post it: "Is it possible to create a set analysis expression by unioning dinamically some sets?".

I have written a static sample, that functions:

sum({$<Year = {2010}, Month = {">=7"}> + $<Year = {2011}, Month = {"<=3"}>} DAYS)

Is it possible to render dynamic this static expression? Yes or not, please? Each set depends on two dimensions.

I can know how many sets only at run-time.

Thanks



Not applicable
Author

Moreover, I could have two different dimensions instead of Year and Month, fe ProductCode and CustomerCode. This can be a generic question that could be useful for entire community.

Many thanks.

ToniKautto
Employee
Employee

Please see attached example of how you can use a master calendar table, in order to utilize set analysis to make date limited selections.

I hope this can helpful as an example on how calendar limitations can be made when using Set Analysis. My suggestion is that you apply a master calendar table also to your app, to get a unified an easy to apply date measurement.

If there is anything unclear with the sample doc, please let me know and I will try my best to explain. Else if this resolves your problem, please mark this thread as resolved.

Not applicable
Author

Thanks Toni, but I think I explain better the issue.

I don't want the result on the "Year To Month" chart or on the "First to last month" chart.

I have a tree view list box with year and month and with it I can select more one values for these dimensions.

So I could have these two selections, fe, year = 2010 + month = 3 and year = 2011 + month = 2. I want to show the measures for these dimension values:

  • 2010 01, 2010 02, 2010 03;
  • 2011 01, 2011 02.

Not between 2010 03 and 2011 02.

But I could do also more two selections and so on. Now, is it clear the issue?

Thanks

ToniKautto
Employee
Employee

Let's try again. I have added a pivot table with a calculated dimension, so no usage of set analysis.

Not applicable
Author

No Toni, the behaviour is like your Year To Month char but with the possibility to select MORE year and months. I must show all months between january and the selected months for each selected year.

So, if I select year = 2011 and month = 3 I need to show the measure values for year = 2011 and month equals to 1, 2, 3.

If I select also year = 2010 and month = 2 I need to show the measure values for year = 2010 and month equals to 1, 2, and so on for additional selections.

Thanks

Not applicable
Author

Hi,

I'm solving by constructing the set analysis expression as a VBScript macro code.

I hope to don't use VBScript.

Thanks

ToniKautto
Employee
Employee

It is a very tricky scenario you have suggested, to make these multiple selections and get dynamic presentation.

Any way, here is what I think is the best I can come up with based on the requirements you have set up. In the example I have added 4 expressions (making N <= 4), of which each is set statically to look for the N:th selection in the tree view. This means that you should only make one selection per year, and that the columsn will only be show if the N:th selection has been made.