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

union sets

I have what feels like a common problem. I have a simple line chart that is showing by unit, by time the percent of positive responses to a survey question. Someone has asked that we add a line item for the organizational total and a national benchmark. My thought was to load rows for a "total" unit and for a "Benchmark" unit and then use set analysis to always show the total and the benchmark regardless of the filters and union that with the "regular" set of data subject to the filters the user selects. I am trying to use a statement like this:

count({1 <Main_Unit ={'Totals'} >

+ {$ <Main_Unit =-{'Totals' >} RECID)

I am running into syntax issues. I am starting to think I am not taking the right approach. Does anyone have a syntax suggestion or a more sophisticated way to solve this? Perhaps an Control chart of sorts?

Thanks for your help!

Kyle

5 Replies
JonnyPoole
Employee
Employee

count(    RECID)               -> in a text box, this will count the total RECID for the current selections

count(    RECID)               -> in chart with dimensions, this will count the total RECID for the current selections per                                              dimensional value

count(  TOTAL  RECID)   -> in a chart with dimensions, this will count the total RECID for the current selections                                                        across all dimensional values

count ( {1} RECID)            -> in a text box, this will count the total RECID in the entire data model irregardless of                                                       selections

Do you need the last one ?  Not sure you need any SET ANALYSIS here.

Not applicable
Author

Hi Jonathan:

Thanks for the response. I think it helped me with another issue. I am either missing something you are helping me with or I wasn't clear. The two values that I always want to show in the line graph are not associated with any of the other dimensions. They would be total for our organization and a national benchmark. My attempt to back into this was to create a single row for each with the "Main_Unit" name forced to be "Totals" and "Benchmark". Then in the expression for the chart object union the detail facts subject to normal dimensional filtering excluding the two rows mentioned above

i.e. {$ <Main_Unit =-{'Totals' >} RECID)

with the two rows explicitly selected not subject to the normal filters.

count({1 <Main_Unit ={'Totals'} >

Thanks again for your help!

Kyle

JonnyPoole
Employee
Employee

Hi Kyle , i don't know enough about the required dimensional context to advise either way.  My sense is that you have 2 tables that report the numbers at different aggregation points. The main table has many rows and reports at a detail level with other dimensional values in the table.  The summary table has very few records, maybe just 2 and reports at a summary level with some or none of the same dimensional values reported.

Usually i would find someway to link/associate the two tables (think of them as 2 diff fact tables) whether its a single dimension or a compound key.

If there is absolutely no shared dimensions , i would suggest a data island and then use IF statements in the chart to render the values from the data island when 'summary' is selected and the normal values when 'detail' is selected.

If there are no shared dimensions you probably need to create conditional IF statments on the dimensions too.... and at that point you would want to just hide/show different charts using the layout tab / conditional show expression to show the correct chart.

Does this help ?

Not applicable
Author

Thanks for sticking with this Jonathan. This is very helpful.

The primary feature that I am trying to implement is to have the two summary rows (organization total (Totals) and national benchmark) always visible in the chart regardless of the filters chosen. In my ETL program, I already have the rows loaded into my fact table. See marked up image below showing totals line. I need the totals line to show regardless of which filters are chosen (don't even want to give the option to select or deselect them). I was thinking that this is a union of two sets in the chart expression. The first being all the natural rows for each department, the second being the forced set of the two summary rows using the {1} set.

Thanks again!

Kyle

2014-09-24_9-16-59.png

JonnyPoole
Employee
Employee

OK - so i can think of 2 approaches

1.  A Data model solution that establishes 'Totals' as another value in the Field called 'Unit' . So that it can be shown dynamically in the chart when 'Unit' is the 2nd dimension.

2.  Eliminating Unit as a dimension and create 11 expressions in its place , each with a custom SET ANALYSIS statement.to summarize values for each unit and one for all units (TOTALs)

I think the 2nd is a manual setup with more maintenance but probably quite easy to setup and get you going. Option 1 is potentially more elegant and there could be a creative way of structuring the data model so that TOTALs are calculated dynamically without needing to pre-calculate.  You would only pre-calculate if you want the totals to be completely fixed and not 'sliceable'.