topic Delta between months in Set Analysis in QlikView

Delta between months in Set Analysis

c_pannese — Wed, 13 Dec 2017 09:11:00 GMT

Hi ,

I have a situation like this:

DATE	Code	N. Piece	Amount
201705	A	5	50
201705	B	8	100
201705	C	6	75
201706	A	12	80
201706	B	15	122
201706	C	10	90
201707	A	19	101
201707	B	22	134
201707	C	21	98

The Date format is 'YYYYMM' .

Every month has about 130.000 rows and the Measures are cumulative from the previous month. What I need is calculate the Delta between months and I cannot use function like "Before" or "Above".

So I thought to use Set Analysis but when I use Date-Function for filter by the previous month the sistem returns me 0 or in others cases the identical value of the month instead of the previous.

I hope to be exhaustive.

Can someone help me please?

Thank you All.

Re: Delta between months in Set Analysis

sunny_talwar — Thu, 21 Dec 2017 17:00:40 GMT

May be implement The As-Of Table

Re: Delta between months in Set Analysis

sunny_talwar — Thu, 21 Dec 2017 18:01:17 GMT

or share a sample to show your issue? I wonder why you can't use Above or Below?

Re: Delta between months in Set Analysis

uacg0009 — Fri, 22 Dec 2017 03:20:57 GMT

Hi Chiara,

if the measure is cumulative like you said, then I think the discrepancy between months is just the delta, isn't it?

Thanks

Aiolos

Re: Delta between months in Set Analysis

c_pannese — Fri, 22 Dec 2017 10:31:43 GMT

It works!

I cannot use "Above" or "Below" because they don't work with the selection on a single month.

Thank you very much for you help!

Bye

Re: Delta between months in Set Analysis

hitman1967 — Fri, 22 Dec 2017 11:40:39 GMT

Hi Chiara,

this is an implementation with the AsOfTable. I hope this can help you.

BR,

Sandro

Re: Delta between months in Set Analysis

sunny_talwar — Fri, 22 Dec 2017 16:22:28 GMT

There are ways to make Above/Below work with selection of Month also...

Above(Sum({<Month>}Sales)) * Avg(1)

Here Sum(Sales) will ignore selection in Month field to pull the value of previous dimension to this dimension and Avg(1) will make sure that only current dimension is shown as Avg(1) = 1 for current selections and 0 for out of selections

Re: Delta between months in Set Analysis

c_pannese — Mon, 22 Jan 2018 16:31:00 GMT

Hi Sunny,

at the moment I have a new issue.

I need to create the same process but for successive months.

In january 2017 I have to use the value of february 2017 and in other case march 2017.

Is it possible?

Thank you!

Re: Delta between months in Set Analysis

sunny_talwar — Mon, 22 Jan 2018 16:58:55 GMT

Should be possible... can you elaborate a little more and may be share a sample?

Re: Delta between months in Set Analysis

c_pannese — Tue, 23 Jan 2018 09:07:19 GMT

Here is an example.

I have to reproduce the same behavior of the "RESULT A" and "RESULT B".

I could sum the mesures using "MonthDiff" but how can I get the numerator?

Re: Delta between months in Set Analysis

s_santalucia — Thu, 01 Feb 2018 10:38:11 GMT

Hi Chiara,

this is possible if you remove the where condition in

<< as-of-table>>

Regards

Stefania

Re: Delta between months in Set Analysis

c_pannese — Thu, 01 Feb 2018 11:04:56 GMT

Hi Stefania,

you're right!!

thank you!

Bye Chiara