topic Re: Sparkline for a 6 month period in QlikView

Sparkline for a 6 month period

snehamahaveer — Mon, 19 Oct 2015 11:15:43 GMT

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.

Re: Sparkline for a 6 month period

petter — Mon, 19 Oct 2015 11:30:12 GMT

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?

Re: Sparkline for a 6 month period

miguelbraga — Mon, 19 Oct 2015 11:33:57 GMT

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

Re: Sparkline for a 6 month period

snehamahaveer — Mon, 19 Oct 2015 11:35:11 GMT

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

Re: Sparkline for a 6 month period

snehamahaveer — Mon, 19 Oct 2015 11:38:33 GMT

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

Re: Sparkline for a 6 month period

miguelbraga — Mon, 19 Oct 2015 11:45:24 GMT

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)

=sum(aggr(rangesum(above(total sum({<Finmonth=>} Sales),0,6)),Finmonth))

Re: Sparkline for a 6 month period

snehamahaveer — Mon, 19 Oct 2015 12:47:25 GMT

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.

Re: Sparkline for a 6 month period

miguelbraga — Mon, 19 Oct 2015 13:04:55 GMT

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

Re: Sparkline for a 6 month period

snehamahaveer — Mon, 19 Oct 2015 13:19:28 GMT

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.

Re: Sparkline for a 6 month period

Mon, 19 Oct 2015 13:24:51 GMT

Can you please post qvw with sample data.

Re: Sparkline for a 6 month period

miguelbraga — Mon, 19 Oct 2015 13:26:33 GMT

You can make some changes in your qvw like this:

AsOfFinmonth:
load 'Current' as Type,
Finmonth as Finmonth_AsOf,
Finmonth as Finmonth
Resident SalesData;

Concatenate (AsOfFinmonth)
load 'Rolling 6' as Type,
Finmonth as Finmonth_AsOf,
Finmonth + 1 - IterNo() as Finmonth
Resident SalesData
while IterNo() <= 6;

right join load Finmonth Resident SalesData;

And then in the trend you can simply have this set analysis expression:

sum({<Type={'Rolling 6'}>}Sales)

This way, I think and I hope it can solve all your needs