Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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.
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.
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!!
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)?
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.