topic Re: Linear table different from chart in QlikView

Linear table different from chart

Tue, 24 Mar 2015 10:38:17 GMT

I am working on a table which represents the sold products over 12 months for 5 years.

I used the linear table (in graph) to find the cumulative value of turnover for each year by using this expression:

=num(rangesum(above(sum({<My_year=,My_month=>}vSold), 0 , 12)), '€ #.##0')

which works perfectly. The amount is cumulative from Jan to Dec of same year then it restarts as Jan for the next year and so on.

However when I use the same formula into the graph (bar graph) it does not work in the same way:

For instance on Jan 2013 I do not have the value of this specific month but an incremental value starting from Dec 2012.

By calculation it does not result to be the correct value of Dec 2012 + Jan 2013: I do not understand where this value comes from.

Note:

To have the right value of Jan 2013 as cumulative (Dec 2012 + Jan 2013) I have to use this expression:

=rangesum(above(sum({<MonthYear=>}vSold), 0 , RowNo()))

but the result is not what I want. (It works not even with cumulative or progressive set)

To be noted that on graph I used a different dimension which is MonthYear (taken from script) to be able to see year and month as x-axis.

Date(MonthStart (Mese), 'MMM-YYYY') as MonthYear

I tried to set the expression for the graph as

=num(rangesum(above(sum({<My_year=,My_month=>}vSold), 0 , 12)), '€ #.##0'): does not work

=num(rangesum(above(sum({<MonthYear=>}vSold), 0 , 12)), '€ #.##0'): does not work

=num(rangesum(above(sum({<My_year=,My_month=,MonthYear=>}vSold), 0 , 12)), '€ #.##0'): does not work

Sorry to be so lengthy.

Can anyone help me?

Thanks

Re: Linear table different from chart

marcus_sommer — Tue, 24 Mar 2015 14:53:42 GMT

I think your problem is MonthYear which isn't a period - it's a date. Try to add MonthYear as period-field within your master-calendar.

- Marcus

Re: Linear table different from chart

Tue, 24 Mar 2015 14:58:22 GMT

Thank you Marcus,

how can I do that?

could you show me clearly the steps?

Thank you again

Re: Linear table different from chart

marcus_sommer — Tue, 24 Mar 2015 15:02:21 GMT

Have a look here: http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar. But there are a lot more examples to create a master-calendar in this forum.

- Marcus

Re: Linear table different from chart

Tue, 24 Mar 2015 16:40:31 GMT

Marcus,

I tried to create this:

CalendarMaster:
Load Distinct
Mese,
Month(Mese) as MC_Month,
Year(Mese) as MC_Year,
Date(MonthStart (Mese), 'MMM-YYYY') as MC_MonthYear
resident [da BAAN YTD];

where the table [da BAAN YTD] has been loaded previously

LOAD

Mese,
Month(Mese) as My_month,
Year(Mese) as My_year,
Date(MonthStart (Mese), 'MMM-YYYY') as MonthYear,
....
Quantità

FROM

(ooxml, embedded labels, table is [da BAAN YTD]);

Then I used the new MC_MonthYear as dimension for my graph but nothing change. Is there a mistake in my Master Calendar?

Massimo

Re: Linear table different from chart

Tue, 24 Mar 2015 17:45:19 GMT

Marcus,

I found out that chart works if I use My_year and My_month as dimensions.

Where

Month(Mese) as My_month,

Year(Mese) as My_year

since Mese is defined in my excel file as JAN-2010, FEB-2010,...., DEC-2014

The problem is that I need x-axis with month and year in the same dimension so it has been suggested to state

Date(MonthStart (Mese), 'MMM-YYYY') as MonthYear

Which could be the problem if all of them come from Mese?

Why graph and table work well with My_month & My_year dimensions but not with MonthYear?

Note:

I also tried to load Mese and use it into the dimension of chart but it does not work.