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

# 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

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.

• ###### Re: Alternate States and Synthetic Dimension

Gysbert,

thank you that you have spent your time on this task and understood it.

Unfortunatelly, i cannot open the document you have attached, because of restrictions of my computer. Could you please describe here, what have you chenged in it. Appreciate it.

Best Regards,

Sergey.

• ###### Re: Alternate States and Synthetic Dimension

All Right, i have got it.

The solution is not very buautiful, because i have some more metrics to calculate(not just TotalDue), but it works.

Thank you!!

• ###### Re: Alternate States and Synthetic Dimension

Gysbert,

I have done as you commented, and i have ugly table , because i also have other metrics to calulate(SubTotal and others, not shown).

I have attached two pictures, the first one with dimension on Year, the second one without. Is there a possibility to make it prettier?

Thank you!

P.S.how you think, is there any possibility to make this task ANY other way(maybe using macroses and triggers)?

• ###### Re: Alternate States and Synthetic Dimension

You could try using text boxes instead. Since the expressions are supposed to return only one number, not a set of rows like your example with the Year dimension, you can use the expressions in text boxes too. Then you can add as many text boxes for labels and expressions as you want and format them so the whole looks pretty. This is more work than using a straight table or pivot table object, but you have a lot more flexibility in layout and formatting.