Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using images to present year-over-year trend data at a glance. I want the arrow under 'Division' to always reflect the division's trend overall and the arrow under 'Channel' to change based on selected channel data. The purpose is so that a channel head drilling down into their own data sees their performance relative to the Division as a whole - without having to reselect all data.
When I 'detach' the object for Division, this works perfectly. However, every time I reload data or re-open the file in QlikView, all objects are attached again. Will this behavior change once the document is published, or am missing something in the configuration at the document or object level? The arrow objects are charts, configured as a straight table, where the expression is represented by an image.
Since Detach is more for users and not for developers, I would assume you will see the same behavior when you publish it. Why don't you ignore the channel selection with set analysis in expression that show the division's tendency?
sum({$<Channel=>} Amount)
Regards.
Since Detach is more for users and not for developers, I would assume you will see the same behavior when you publish it. Why don't you ignore the channel selection with set analysis in expression that show the division's tendency?
sum({$<Channel=>} Amount)
Regards.
Hello Karl. I'm fairly new and haven't worked much, yet, with set analysis. I replaced my expression with yours, and it works: the arrow doesn't change even after I select a smaller data set. However, I only want the image to be based on a comparison of the value of one month this year vs. the same month last year. I am using the following expressions:
Expression 1 sums the total ACP for a certain month last year
=sum(if((time_year=$(vLastYear)) and Num((Month(activity_date))=($(vLastMonth))),ACP))
Expression 2 sums the total ACP for the same month this year
=sum(if((time_year=$(vCurrentYear)) and Num((Month(activity_date))=($(vLastMonth))),ACP))
Expression 3 calculates the difference between Current Year value and value Last Year
=Column(2)-Column(1)
Expression 4 is used to express the difference as a percentage
=(Column(2)-Column(1))/Column(1)
Expression 5 is simply used to determine which arrow (up or down) to display
=if((Column(2)-Column(1))<0 ,'qmem://<bundled>/BuiltIn/arrow_s_r.png','qmem://<bundled>/BuiltIn/arrow_n_g.png')
How would I incorporate set analysis into this?
The expressions would be something like the following:
Expression 1 sums the total ACP for a certain month last year
=sum({$<Channel=>} if((time_year=$(vLastYear)) and Num((Month(activity_date))=($(vLastMonth))),ACP))
Expression 2 sums the total ACP for the same month this year
=sum({$<Channel=>} if((time_year=$(vCurrentYear)) and Num((Month(activity_date))=($(vLastMonth))),ACP))
Expression 3 calculates the difference between Current Year value and value Last Year
=Column(2)-Column(1)
Expression 4 is used to express the difference as a percentage
=(Column(2)-Column(1))/Column(1)
Expression 5 is simply used to determine which arrow (up or down) to display
=if((Column(2)-Column(1))<0 ,'qmem://<bundled>/BuiltIn/arrow_s_r.png','qmem://<bundled>/BuiltIn/arrow_n_g.png')
Regards.
Worked perfectly! Thank you so much.
Hi Karl,
I am having a similar issue that Ellen had yesterday where my detached chart won't remain detached. Not sure how to follow your fix based on Ellen's expressions. Maybe you can help me with mine?
Upon opening my sheet, I have set some triggers on the sheet properties so that the entire sheet opens with certain actions in place. There is one object on the sheet that I don't want to follow these trigger actions. When I clear selections, select detach on the object, tab out and tab back in, my object is still detached and works fine. But when I save and close, then re-open, the detached object is now unattached.
Currently my expressions in this object (which is a pivot table) look like this and I want them all to ignore the triggers I have in place:
Expression 1 sums all revenue
Sum (Revenue)
Expression 2 sums all transaction count
Sum
([Transaction Count])Expression 3 counts all the "orderable SKUs"
count({<[Is SKU a Migration?] = {'N'}, [Release Version] = {'0','1','2','3','4','5','6','7','8','9','10','11','12','13','21','23','2006','2007','2008','2009','2010','2011','3.7','2009.1'}, [Material Group] = {'License','Bundles','Coursewr','elicense','Services','Subretro','Subscrpt2','Subscrptn','Support','Training'},[Release Type] = {'blank','F'}>} (MaterialNumber))
Expression 4 counts all the migration SKUs
count({<[Is SKU a Migration?] = {'Y'}>} (MaterialNumber))
Expression 5 counts all the "non-orderable" SKUs
Count (MaterialNumber)-(count({<[Is SKU a Migration?] = {'Y'}>} (MaterialNumber))+count({<[Is SKU a Migration?] = {'N'}, [Release Version] = {'0','1','2','3','4','5','6','7','8','9','10','11','12','13','21','23','2006','2007','2008','2009','2010','2011','3.7','2009.1'}, [Material Group] = {'License','Bundles','Coursewr','elicense','Services','Subretro','Subscrpt2','Subscrptn','Support','Training'},[Release Type] = {'blank','F'}>} (MaterialNumber)))
Expression 6 counts all the active SKUs
Count
(MaterialNumber)What type of triggers do you have in place?
On sheet properties > on activate sheet, I have added triggers to select certain selections automatically. But I have a trigger that says to exclude the object I want detached from all the selections.
If you want to ignore all selections put a one in front of your set analysis:
count({1<[Is SKU a Migration?] = {'Y'}>} (MaterialNumber))
or for every field selection you want to ignore put Field1=
count({$<Field1=,Field2=,Field3=,[Is SKU a Migration?] = {'Y'}>} (MaterialNumber))
I hope that helps.
Regards.
Thanks Karl. But what if the expression is not using a set analysis and I want it to ignore the selections? Example:
count
(MaterialNumber)