topic Re: Complex Time dimension in QlikView

Complex Time dimension

l_boccasso — Tue, 03 Nov 2015 16:13:28 GMT

Hello,

I have to build a chart like the one picture.

Time dimension (on x axis) is dynamic and it has 4 elements YTD, Last YTD, Full Last Year, Full two Last Year.

Users can change month and year of analysis.

How can I build this dimension? Calculated dimension?

Thanks.

ElizaF — Tue, 03 Nov 2015 21:28:12 GMT

Hi Luca,

The solution can be:

1. To use Alternate States (vers. 11+) in the chart - to avoid any change of the data according the user selection.

2. For calculate value in the chart use Set Analysis function:

Current Year - Sum( {$ <CalYear={ $(=max(CalYear)) }>} SalesAmount)

LYTD - Sum( {$ <CalYear={ $(=max(CalYear)) }>} SalesAmount*_FLAGLYTD)

Full last Year - Sum( {$ <CalYear={$(=max(CalYear)-1)}>} SalesAmount)

Full two Year - Sum( {$ <CalYear={$(=max(CalYear)-2)}>} SalesAmount)

_FLAGLYTD =inyeartodate (DataSales, Today(), -1 ) - calculated in scrip.

Hope help you!

Qrishna — Tue, 03 Nov 2015 21:31:57 GMT

I guess its possible.Let me give a try ,if u can provide some sample data.

jpapador — Tue, 03 Nov 2015 21:55:43 GMT

assuming you had a YTD flag in the script you could do something like below:

Dimension:

Calculated: =Valuelist('YTD 2014', 'LYTD 2015', '(full)2014', '(full)2013')

Expression: 1 long IF statement

If(Valuelist('YTD 2015', 'LYTD 2014', '(full)2014', '(full)2013') = 'YTD 2015',

Sum({$<Year={$(=Max(Year))}, YTDFlag={1}>} Sales),

If(Valuelist('YTD 2015', 'LYTD 2014', '(full)2014', '(full)2013') = 'LYTD 2014',

Sum({$<Year={$(=Max(Year)-1)}, YTDFlag={1}>} Sales),

if(Valuelist('YTD 2015', 'LYTD 2014', '(full)2014', '(full)2013') = '(full)2014',

Sum({$<Year={$(=Max(Year)-1)}>} Sales),

f(Valuelist('YTD 2015', 'LYTD 2014', '(full)2014', '(full)2013') = '(full)2013',

Sum({$<Year={$(=Max(Year)-2)}>} Sales)))))

They would still be able to make month and year selections.

l_boccasso — Wed, 04 Nov 2015 08:56:04 GMT

thank you jpapador, you found the solution!