How to make a better way to do the subfield in dimension

[uacg0009]

Hi all,

I have data like:

the fourth column is what he bought before this purchase.

I just want to show like:

IF I choose ITEM=A in the listbox then show a chart like:

I just want to show how many vips bought what ITEM before they buy ITEM=A(in this example).

I hope you know what I want to calculate, I don't know whether there is a function to calculate that or some way to do the subfield.

If you have any question please tell me, thank you.

[#2]

Please find attachment.

Hope this will solve your issue.

[rustyfishbones]

You can use Set Analyses in your expression

1-$ represents the inverse of the records selected

eg. SUM({1-$} Sales)

[#4]

Temp:

LOAD * INLINE [

    VIP, ITEM, TIME, HISTORY_ITEM

    1001, A, 2013-01-01

    1001, C, 2013-01-07, A

    1001, D, 2013-01-12, A+C

    1001, E, 2013-02-01, A+C+D

    1001, E, 2013-02-14, A+C+D+E

    1002, B, 2013-01-11

    1002, B, 2013-01-21, B

    1002, A, 2013-02-01, B

    1002, D, 2013-02-22, B+A

    1003, C, 2013-01-04

    1003, F, 2013-01-06, C

    1003, A, 2013-01-15, C+F

    1004, E, 2013-01-14

    1004, B, 2013-02-01, E

    1004, D, 2013-03-01, E+B

    1004, A, 2013-03-21, E+B+D

];

NoConcatenate

Temp2:

LOAD

VIP, ITEM, TIME,

Mid(HISTORY_ITEM&'+', Index(HISTORY_ITEM&'+','+',IterNo())-1,1)   AS HISTORY_ITEM

Resident

Temp

While Index(HISTORY_ITEM&'+','+',IterNo())>0

;

DROP Table Temp;

[uacg0009]

Thank you, but it seems not what I want.

Because when I choose ITEM=A,

We can see that only 2 vips(1002,1004) bought B before they bought A, only one vip(1003) bought C, one bought D one bought E one bought F.

But the result you gave me is not correct, do you have any other idea to solve that? thank you.

[uacg0009]

Thank you, but like I reply to priya above, I think it is not what I want.

So could you have any other idea to solve that?

[#7 · Accepted solution]

Hi Changjun,

I'am interested what is your external system data structure.

Do you get this field with previous products history (D+A+...) from source system or you preparing it somewhere during QV or warehgouse data preparation.

It may be also important what is your data volume and what another KPI and selections possibilities do you need in your application.Anyway my suggestion is to have this historical items as 2'nd table.

From your data source deppends how to create it. But having it you will have more possibilities. For example you will be able make some selections on this historical item list....

Look on my example.

I hope you like it. Let me know if it helps.

regards

Darek

[uacg0009]

Thank you so much, your answer is very useful!

[uacg0009]

Thank you so much,Dariusz, your answer is very comprehensive and it gives me inspiration.

[#10]

Does my answer resolve your problem or you need more help?