Pivot Table

[soha1902]

Hi All,

I have following Data:

ProdID	Product	Activated	Deactivated
1	Prod A	02/15/13
2	Prod B	08/05/13
3	Prod C	01/07/14
1	Prod A		02/10/14
2	Prod B		02/26/14
1	Prod A	11/15/14

Now my requirement is, if any Product is active then its price will be display as 10 and if it is deactive then its price will be 0.

For example Prod A is activated on 15 feb 2013 and deactivated on 10 Feb 2014 so from 15th Feb 2013 till 10Feb 2014 its price will be 10. On 10th Feb 2014 Prod A was deactivated and on 15 Nov2014 again got activated so from 10th Feb 2014 to 15 Nov 2014 its price will be 0.

I need to add Price column as a expression and Week column should be pivoted.

Can any one suggest me, how to implement this.

The resultant table shold look like this for Product A

ProdID

Product

Activated

Deactivated

2/18/2014

2/25/2014

-------------'

2/7/2014

2/14/2014

2/21/2014

-------------'

11/14/2014

11/21/2014

till cureent date

1

Prod A

02/15/13

10

10

10

0

0

0

10

10

2

Prod B

08/05/13

3

Prod C

01/07/14

1

Prod A

02/10/14

2

Prod B

02/26/14

1

Prod A

11/15/14

Thanks in advance.

[soha1902]

any suggestions please.....

[saumyashah90]

What about products which doesnt have deactivation date... for example Prod C?

[soha1902]

it will always show Price =10 because it never got deactivated.....

[saumyashah90]

Also the Dates shown above should be what?....i mean what should be the period of the dates in the horizontal line?

[soha1902]

Those dates are weekly dates.

[lironbaram]

hi have a look at the attach example

have a look at the script where most of the work is done

[#8]

I know it's maybe a stupid question, but I can see from your example that Prod A  (ID=1) has two activation date...

I think you should add an expression: if=(num(Today())>num(Exp.date),0,10). Then add date as a dimension. I suppose, data is  present in the DB from activation date, therefore you shouldn't have a date renge exceeding the range of the to be analysed dates.

Br,

T

[soha1902]

Hi Liron,

Thanks for your help. It showing correct values for Product A and Product C but not for Product B. Please see below image:

For Product B it is showing Deactivated date is current date but it excel it was 02/26/2014. see below:

I attached both qvw and excel file in attachment. Please help to solve this.

[lironbaram · Accepted solution]

hi

fix it

have a look