topic Re: Calculated Dimension with Set Analysis in QlikView

Calculated Dimension with Set Analysis

taylor_jesse — Wed, 25 Nov 2020 16:16:04 GMT

I have a set of data from 2015 to 2018 however I will like to create a calculated dimension for a chart that only displays data from 2016 to 2018 but I will also add a filter to the tab where the user can select to see the 2015 data if they want to just won't show in the chart as the default view.

Any help.

Thank you

Re: Calculated Dimension with Set Analysis

Anil_Babu_Samineni — Thu, 10 May 2018 04:05:26 GMT

Let's talk in 2 tasks

I have a set of data from 2015 to 2018 however I will like to create a calculated dimension for a chart that only displays data from 2016 to 2018

Instead of creating dimension - I suggest you to restrict in expression

Sum({<Year = {2016, 2017, 2018}>} Sales)

but I will also add a filter to the tab where the user can select to see the 2015 data if they want to just won't show in the chart as the default view.

Not entirely sure, But whole expression should look like this

If(GetSelectedCount(Year)=0, Sum({<Year = {2016, 2017, 2018}>} Sales), Sum(Sales))

Re: Calculated Dimension with Set Analysis

Anonymous — Thu, 10 May 2018 06:50:28 GMT

Add Calculated Dimension like below

=if(Year>2015,Year)

Re: Calculated Dimension with Set Analysis

jyothish8807 — Thu, 10 May 2018 07:02:37 GMT

So if user select 2015 do you want to show sale from 2015 to 2018 ? or only 2015 ?

Br,

Re: Calculated Dimension with Set Analysis

shiveshsingh — Thu, 10 May 2018 07:23:52 GMT

for you second requirement, you can create a separate chart and set the visibility like if selected year = 2015, then show the chart with 2015 values.

Re: Calculated Dimension with Set Analysis

jyothish8807 — Thu, 10 May 2018 07:41:59 GMT

Try like this:

If(GetFieldSelections(Year)='2015',Sum({<Year = {2015}>} Sale), Sum({<Year = {2016, 2017, 2018}>} Sale))

Br,

Re: Calculated Dimension with Set Analysis

taylor_jesse — Thu, 10 May 2018 13:34:04 GMT

User can select 2015 data to view it but the default view when all selections are cleared should display data from 2016 to 2018.

Re: Calculated Dimension with Set Analysis

taylor_jesse — Thu, 10 May 2018 13:35:38 GMT

Do you want me to try this as the calculated dimension to as an expressions for the chart?

Thank you

Re: Calculated Dimension with Set Analysis

javierortiz79 — Thu, 10 May 2018 13:49:34 GMT

Put this in an expression:

If(GetSelectedCount(Year)>0,Sum({<Year = {'$(=Max(Year))'}>} Sale) , Sum({<Year = {'2016','2017','2018'}>} Sale))

Re: Calculated Dimension with Set Analysis

vijetas42 — Thu, 10 May 2018 14:13:21 GMT

do you want particular chart to show default data 2016 to 2018 or whole dashboard?

If you want whole dashboard to show default data for 2016 to 2018 then you can apply document level trigger

or if you want for any particular chart then you can create two different calculated dimension one when user select any of the year then,

expression, sum(sale)

condition,GetSelectedCount(year)>0

and other expression should be,sum({<year={'2016','2017','2018'}>}sale)

condition,GetSelectedCount(year)=0

Re: Calculated Dimension with Set Analysis

taylor_jesse — Thu, 10 May 2018 14:38:02 GMT

This is the expression I am currently using

=sum({$<metric_name={'Non-Billable Cost'}>} metrictotal)/sum(hours_units)

How can I adjust this to fit your expression or do you think possibly I calculated dimension will be best. My dimension now is "Year" which is from 2015 to 2018.

Re: Calculated Dimension with Set Analysis

taylor_jesse — Thu, 10 May 2018 14:40:50 GMT

I am looking to have the default view of a particular chart show from 2016 to 2018 but I will also add a year filter where the user can select to see 2015 data if they want to.

This is the expression I am currently using

=sum({$<metric_name={'Non-Billable Cost'}>} metrictotal)/sum(hours_units)

How can I adjust this to fit your expression or do you think possibly I calculated dimension will be best. My dimension now is "Year" which is from 2015 to 2018.

Re: Calculated Dimension with Set Analysis

taylor_jesse — Thu, 10 May 2018 14:42:19 GMT

This is the expression I am currently using

=sum({$<metric_name={'Non-Billable Cost'}>} metrictotal)/sum(hours_units)

How can I adjust this to fit your expression or do you think possibly I calculated dimension will be best. My dimension now is "Year" which is from 2015 to 2018.

Are you suggesting I use your expression as a calculated dimension.

Re: Calculated Dimension with Set Analysis

taylor_jesse — Thu, 10 May 2018 14:42:39 GMT

This is the expression I am currently using

=sum({$<metric_name={'Non-Billable Cost'}>} metrictotal)/sum(hours_units)

How can I adjust this to fit your expression or do you think possibly I calculated dimension will be best. My dimension now is "Year" which is from 2015 to 2018.

Re: Calculated Dimension with Set Analysis

javierortiz79 — Thu, 10 May 2018 15:00:22 GMT

The set Analysis will limit the dimension and the result to 2015 if selected or 2015,2016,2016, you don't need a calculated dimension.

put this in an expression.

=IF(GetSelectedCount(Year)>0,Sum({<Year = {'$(=Max(Year))'},metric_name={'Non-Billable Cost'}>} metrictotal)/sum(hours_units) , Sum({<Year = {'2016','2017','2018'},metric_name={'Non-Billable Cost'}>} metrictotal)/sum(hours_units))

Re: Calculated Dimension with Set Analysis

taylor_jesse — Thu, 10 May 2018 15:45:28 GMT

Thank you so much for your quick response. With this expression will the user be able to select and view 2015 data?

Re: Calculated Dimension with Set Analysis

javierortiz79 — Thu, 10 May 2018 15:56:37 GMT

Yes but just if they select the 2015, if not, they will see from 2016 to 2018.

Re: Calculated Dimension with Set Analysis

taylor_jesse — Thu, 10 May 2018 20:01:13 GMT

Please see the image. The 2015 line is actually flat now

Re: Calculated Dimension with Set Analysis

Anil_Babu_Samineni — Fri, 11 May 2018 02:02:07 GMT

May be this?

If(GetSelectedCount(Year)=0, sum({$<metric_name={'Non-Billable Cost'}, Year = {2016, 2017, 2018}>} metrictotal)/sum({<Year = {2016, 2017, 2018}>}hours_units), sum({$<metric_name={'Non-Billable Cost'}>} metrictotal)/sum(hours_units))

Re: Calculated Dimension with Set Analysis

taylor_jesse — Fri, 11 May 2018 14:41:20 GMT

This expression worked however the legend still shows 2015 but the lines just show 2016 to 2018. How can I not make the legend show 2015?

Thank you