topic Re: SET analysis required. in QlikView

SET analysis required.

varunvarma — Tue, 25 Nov 2014 11:39:52 GMT

Dear All,

Greetings for the day.

Please guide me to achieve below requirement. Please find the attachment for sample data.

I need to create a pivot table

DIMENSION :- TEST_YEAR

EXPRESSION :- FORMULA (Please find the image below).

For TEST_YEAR (2015) --> I mentioned formula in above image.
i.e (value of TEST_COUNT FOR MIN(TEST_SALEYEAR)(6) * (TEST_YEAR - TEST_SALEYEAR(MIN)) + ......)/ (SUM OF ALL TEST_COUNT FOR TEST_YEAR).

Hope you understand my requirement. Please support. Thanks in advance.

Re: SET analysis required.

varunvarma — Wed, 26 Nov 2014 03:21:08 GMT

Dear All,

Any solution for above requirement ???

Re: SET analysis required.

jyothish8807 — Wed, 26 Nov 2014 05:11:57 GMT

Hi varun,

Try this expression:

Take Test Year as dimension.

In expression:

aggr(firstsortedvalue(TEST_COUNT,TEST_SALEYEAR),TEST_YEAR)*((TEST_YEAR)-aggr(min(TEST_SALEYEAR),TEST_YEAR))/sum(Total <TEST_YEAR> TEST_COUNT)

Regards

Re: Re: SET analysis required.

jagan — Wed, 26 Nov 2014 05:18:14 GMT

Hi,

Please find attached file for solution.

Regards,

jagan.

Re: SET analysis required.

jyothish8807 — Wed, 26 Nov 2014 05:18:25 GMT

This is for just one value, if you want to keep incresing the min(Year) then you have to use loop concept and create a script in the backend.

Regards

Re: Re: SET analysis required.

jagan — Wed, 26 Nov 2014 05:20:16 GMT

Hi,

In attached file I tried like this

Chart : Pivot Table

Dimension: TEST_YEAR

Expression:

=If(TEST_YEAR = Year(Today()) + 1,

Sum(Aggr((TEST_YEAR - TEST_SALEYEAR) * TEST_COUNT, TEST_YEAR, TEST_SALEYEAR))/

Sum(TEST_COUNT),

Sum(TEST_COUNT))

Hope this helps you.

Regards,

jagan.

Re: SET analysis required.

varunvarma — Wed, 26 Nov 2014 08:38:22 GMT

Thank you jagan garu..Because of no time i completed in back end but this is really help me.

Re: SET analysis required.

jyothish8807 — Wed, 26 Nov 2014 09:04:16 GMT

Hi ,

Can you share the script you used in the backend?

Regards

Re: SET analysis required.

varunvarma — Wed, 26 Nov 2014 09:44:56 GMT

Hi,

Please look below.

BASE:

LOAD TEST_YEAR,
TEST_SALEYEAR,
TEST_VIN_No
FROM
FINALTEST.QVD
(qvd);

TEMP:
LOAD
TEST_YEAR,
TEST_SALEYEAR,
Count(DISTINCT TEST_VIN_No) as TEST_COUNT
Resident BASE
GROUP BY TEST_YEAR,TEST_SALEYEAR;

DROP Table BASE;

FINAL:
LOAD *,
(TEST_COUNT)* ( NUM((TEST_YEAR) - (TEST_SALEYEAR))) AS FIN_MUL
Resident TEMP;
DROP Table TEMP;

Dim :- TEST_YEAR

Exp :-

round((SUM(FIN_MUL))
/ SUM(TEST_COUNT) )

It is almost same as jagan's expression.