Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I am trying to work on sparklines in my straight table and I guess it must be my ignorance that I am unable to solve this simple issue.
I have created the table with 2 dimensions - customer and product, then 3 expressions - YTD, YTD previous year, Trend(sparkline).
My issue's are:-
1. I am unable to keep the trend constant - 6 month period
2. I have Financial month loaded as Num(finmonth) and when I use set analysis for sparkline mini chart as =sum({<Finmonth = {'>$(=Max(Finmonth)-6) <=$(=Max(finmonth))'}>}Sales) does not bring back a sparkline unless the max(finmonth) is 7 or greater.
How can I ensure that both issues are resolved.
Your help is much appreciated.
How is your finmonth values? Is it a value between 1 to 12 for each year or is it a sequence number of month from a given historic date?
Hi Sneha,
I've analyzed your expression and found two issues that I need to ask you:
1) Your expression is set to only give you any output if the month is great or equal that 7 beacause of this (=Max(Finmonth)-6) -> If the Finmonth is let say 5 the return value would be -1 and there is no month with -1 value;
2) If the expression was totally right there would be an error still because you've write the expression with one field wrongly, it should be this
=sum({<Finmonth = {'>$(=Max(Finmonth)-6) <=$(=Max(Finmonth))'}>}Sales)
not like this
=sum({<Finmonth = {'>$(=Max(Finmonth)-6) <=$(=Max(finmonth))'}>}Sales)
I'll try to modify your expression to get it right
Hi Peter, The months are created in this method and are numbers from 1-12 (Apr to Mar in year) if(Month(INVOICE_DATE)>3,Num(Month(INVOICE_DATE)-3), Num(Month(INVOICE_DATE)+9 )) as FINMONTH
Hi Braga, Thanks for your reply. It was my typo here. My expression is displaying current fields (Finmonth). Would you be able to help with the first issue? How can I change my field to reflect the correct data. Thanks
After reading this post:
Calculating rolling n-period totals, averages or other aggregations
I managed to get these 2 expression for you to try:
=sum({<Finmonth={'>=$(=only(Finmonth)-5)<=$(=only(Finmonth))'}>} Sales)
or
=sum(aggr(rangesum(above(total sum({<Finmonth=>} Sales),0,6)),Finmonth))
Thanks Braga, Please forgive my ignorance, could you please explain the 'only' function.? When I use only should I select only 1 month? Aggr formula brings back sparkline for a few customers and not for all.
Here Sneha have a look of Mr. hic document:
I made the only so that when you select one appear the result of your selection. Try to change te aggr formula to simply this:
=rangesum(above(total sum(Sales),0,6))
Let me know your results
Hi Braga,
The only function works when I select one particular month (view capture) - but again it only shows trend (sparkline) from the start of the financial year. If I select month as June ( 3 ) then the sparkline displays only for 3 months and not before that. I have a feeling I know why it occurs like that - delivery address were changed(id's included) this year and hence the same is reflected in this chart.
aggr works (view capture 1) when no selection for the month is made.
Can you please post qvw with sample data.