5 Replies Latest reply: Aug 16, 2013 11:23 AM by Gysbert Wassenaar RSS

    Alternate States and Synthetic Dimension

      Hi Guys,

       

      I am trying to build a qlikview report where there is a possibility to compare sales for periods that are defined by custom dates. For example, a user chooses a year of 2005 and monthes of May, April, March, then, he wants to copare them with the following data, a year of 2008 and monthes of December, January, February. To solve it, i use Pivot table to show my data and two ListBoxes for yeach period to choose dates(the first year and the first mothes are in the alternate state "time1", the second year and monthes are a state "time2"). In addition, as i don't have Dimension, i use Synthetic Dim. as ValueLoop().

       

      Problems:

      1. QlikView does not correct recognise the year the sales are calculated(the calculations are correct!), it only shows the value from valueloop, that goes next after selected period 1(if i choose the year of 2005, QV shows 2006).
      2. [Using different combinations of code], If a user chooses a year of 2005 from ListBox(state "time1") and monthes of May, April, March(another ListBox with state "time1"), then, he chooses from another ListBoxes(with the state "time2") a year of 2005 and monthes of December, January, February, QlikView shows only the year of 2005 as a single row(I want him to show two rows with 2005 years, but for different mothes).

       

      Here are variables i use:

       

      v_curr_year_1=concat({[time1]} distinct [Year])

      v_curr_year_2=concat({[time2]} distinct [Year])


      Here is the Dimension:

      =Valueloop(2005, 2008)

       

      Here is the expression:

      Pick(

                Match(Valueloop(2005, 2008),

                          '2005','2006','2007','2008'

                          ),

                Sum({<Year = [time1]::Year, Month=[time1]::Month,OnlineOrderFlag={1}>} TotalDue ),

                Sum({<Year = [time2]::Year, Month=[time2]::Month,OnlineOrderFlag={1}>} TotalDue )

                )

       

      How i want to rewrite the expression, but i suppose the ValueLoop do not recognize alternate states:

       

      Pick(

                Match(Valueloop(v_curr_year_1, v_curr_year_2),

                          $(=v_curr_year_1)

                          ),

                Sum({<Year = [time1]::Year, Month=[time1]::Month,OnlineOrderFlag={1}>} TotalDue ),

                Sum({<Year = [time2]::Year, Month=[time2]::Month,OnlineOrderFlag={1}>} TotalDue )

                )

       

       

      I've attached a qvw and jpg that shows what was i working on.

       

       

      Cheers,

      Sergey

        • Re: Alternate States and Synthetic Dimension
          Gysbert Wassenaar

          Your pick-match combination will always pick the same expression for 2006, i.e. the one that uses alternate state time2. So whatever year you select in time2 is used for the row of 2006. You probably want to use something with no dimension (or at least without your calculated dimension) and two expressions. See attached qvw.

           

          Edit: I've created a straight table object with no dimensions and two expression:

          Sum({<Year = [time1]::Year, Month=[time1]::Month,OnlineOrderFlag={1}>} TotalDue )

          Sum({<Year = [time2]::Year, Month=[time2]::Month,OnlineOrderFlag={1}>} TotalDue )

          As labels for the expressions I've used =only({<Year = [time1]::Year>}Year) and =only({<Year = [time2]::Year>}Year)

          I''ve set the Total Mode for the expressions to No Totals and on the Presentation tab I've checked the option Horizontal.