SET analysis required.

[varunvarma]

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.

[varunvarma]

Dear All,

Any solution for above requirement ???

[jyothish8807]

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

KC

[jagan]

Hi,

Please find attached file for solution.

Regards,

jagan.

[jyothish8807]

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

KC

[jagan · Accepted solution]

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.

[varunvarma]

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

[jyothish8807]

Hi ,

Can you share the script you used in the backend?

Regards

KC

[varunvarma]

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.