Sparkline for a 6 month period

[snehamahaveer]

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.

[petter]

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?

[miguelbraga]

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

[snehamahaveer]

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

[snehamahaveer]

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

[miguelbraga]

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))

[snehamahaveer]

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.

[miguelbraga]

Here Sneha have a look of Mr. hic document:

The Only Function

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

[snehamahaveer]

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.

[#10]

Can you please post qvw with sample data.