10 Replies Latest reply: Mar 5, 2015 10:16 PM by James Kerrigan RSS

    Select a field for only a single visualisation?

    James Kerrigan

      Hi guys,

       

      Is there any way I am able to select a field for only a single visualisation?

       

      For example, I have a table and a bar chart on the same page. The table has a drill-down dimension which goes by

       

      Year > Month > Level 1 > Level 2 > Level 3

       

      So in my table I can view the data of Level 3 for a specific month (eg. December).

       

      However, on my bar chart I use the Month dimension as I want to view the trend of the amounts while the table shows me Level 3 for the selected Month (eg. Table shows me December amounts while bar chart shows me July to December trend).

       

      My problem is that when I drill-down to Level 3 on the table I've already filtered out the other months and can no longer view these on the bar chart at the same time (eg. can only view December, instead of July to December)

       

      Is there any way around this?

        • Re: Select a field for only a single visualisation?
          Greeshma K

          Can you share your qvw?

          • Re: Select a field for only a single visualisation?
            Jonathan Dienst

            Hi

             

            You can do this with an appropriate set expression and/or alternate states. If you post a sample application, you will get more specific help on the expressions you need.

             

            Jonathan

            • Re: Select a field for only a single visualisation?
              Timanshu Virmani

              Hi James,

              Use set expression  for that.

              For example if you are calculating Sum of Sales, then write Sum({<Month= >}Sales) , if month is field having month values.

                • Re: Select a field for only a single visualisation?
                  James Kerrigan

                  Hey guys,

                   

                  Sorry I won't be able to post the file on here. But I took a couple pictures to help elaborate my problem.

                   

                  I played around with the set analysis Timanshu, and it nearly works.

                  I used the expression

                   

                  rangesum(above(

                  SUM({1<[Fiscal Month]>}[Month Target])

                  , 0, [Fiscal Month]))

                   

                  Which works fine here.

                  qlikcommunity1.png

                  But when I select a month this happens.

                  qlikcommunity2.png

                   

                  Which is better than what was happening before, but I don't want the data to remain the same on the chart no matter what month I click.

                   

                   

                  EDIT: I just realised that I don't want that set analysis either because then my data won't change based on other selections I make, which I want it to. So that's no good.

                    • Re: Select a field for only a single visualisation?
                      James Kerrigan

                      I've tried using set expressions in my measures in the table to only display one month (eg. Dec), but then the whole formula no longer works when I remove the month dimension in the drill-down, and I end up with data saying $0 in the cell.

                       

                      I need to remove the month in the drill down so I don't select a month because that shows a specific month in my bar chart, but then that breaks the table.

                       

                      I don't know what else to try now.

                  • Re: Select a field for only a single visualisation?
                    Borja Rodriguez

                    I think you should use Set Analysis only in the Expression of the line chart so when you drill down in the table to the Level 3, you can see the data for any month for that Level 3 value in the line chart.

                     

                    Take a look at the example attached.

                     

                    Preview:

                    Untitled.png

                      • Re: Select a field for only a single visualisation?
                        James Kerrigan

                        Hi Borja,

                         

                        That's a great help, thanks! But it's still not working for me properly and I still can't figure out why. I'll post pictures to explain.

                         

                        The left column of my table is the 'drill-down' (Fiscal Month > Financial Year), the middle is the dimension 'Fiscal Month', and the right is my 'Month Target' Measure used in the chart.

                         

                        When I select Fiscal Month in my drill-down, the bar chart month is affected.

                        chart1.png

                         

                        When I select Fiscal Month in the middle column column, it isn't. Just how I want it, and how you have yours set up.

                        chart2.png

                         

                        I'm a bit confused as to why this is happening :/

                      • Re: Select a field for only a single visualisation?
                        Michael Tarallo

                        Hello James, thanks for your questions. There are a lot of good suggestions posted here that mention the use of Set Analysis.

                         

                        Take a look at this video I just posted, to learn more, it may help you see it clearer.

                         

                        Introduction to Set Analysis (video) - Part 1

                         

                        Let us know how you do.

                         

                        Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                         

                        Regards,

                        Mike Tarallo

                        Qlik

                        • Re: Select a field for only a single visualisation?
                          James Kerrigan

                          Sorry for the many replies in such a short time but I am having some strange problems which I'll try to explain as best I can.


                          For my 'YTD Target' I am using a formula to aggregate each of the month values to determine how much has gone into that level from July to whatever month I select.

                           

                          rangesum(above(SUM({<[Fiscal Month]=>}[Month Target]), 0, [Fiscal Month]))

                           

                          This works fine in tables and charts when the only dimension is 'Fiscal Month', but when I add in a drill-down dimension (including Fiscal Month as a drill), the amounts don't aggregate.

                          Another problem is that when I click on a month, the bar chart which uses 'YTD Target' stops displaying data, but the bar chart which uses 'Month Target' remains unaffected.

                           

                          chart1.png

                           

                          chart2.png

                           

                          I believe the error occurs when I use a drill-down dimension with a rangesum expression.

                           

                          I'm thinking I may have to ditch the drill-down to solve this issue, unless you guys have any other way around it.

                           

                          Edit:

                           

                          I should also add that the values in my YTD Target column in the table don't aggregate when the drill-down is in the same table.