Pivot Chart rendering error

[prat1507]

Hi guys

I have data like this

Year, month, Fruits, Price

2017, JAN, Apple, 10

2017, JAN, Banana, 20

2017, JAN, Mango, 30

2017, FEB, Apple, 10

2017, FEB, Banana, 20

2017, FEB, Mango, 30

2017, MAR, Apple, 10

2017, MAR , Banana, 20

2017, MAR , Mango, 30

2018, APR, Apple, 10

2018, APR, PineApple, 15

2018, MAY, Apple, 50

2018, MAY, Banana, 100

Now I want to build a pivot table

Showing data as

Fruits, Month, Sum(Price)

Now I only want to Show the sum of prices for current FY that is 2018 APR, MAY

but I need to Show all the Fruits available in the data. The sum would be 0 for the Fruits which are not available in current FY. Remember that I dont need any extra data except all Fruits in my pivot chart.

[sunny_talwar]

But Mango is not even there in Apr-2018?

[prat1507]

Yes, but its a client request. Is the no way we can achieve this?

I have to Show all the Fruits with all the months for current FY, with the prices of current FY.

Regards

Pratyush

[prat1507]

hi

Can  anyone help please, I need it urgently.

Regards
Pratyush

[effinty2112]

Hi Pratyush,

If you can edit the script this is a kind of dirty way to do it but it might get you out of a hole.

Data:

load * Inline

[Year, month, Fruits, Price

2017, JAN, Apple, 10

2017, JAN, Banana, 20

2017, JAN, Mango, 30

2017, FEB, Apple, 10

2017, FEB, Banana, 20

2017, FEB, Mango, 30

2017, MAR, Apple, 10

2017, MAR , Banana, 20

2017, MAR , Mango, 30

2018, APR, Apple, 10

2018, APR, PineApple, 15

2018, MAY, Apple, 50

2018, MAY, Banana, 100];

Dummy:

Load Distinct Year Resident Data;

Outer Join (Dummy)

load Distinct month  Resident Data;

Outer Join (Dummy)

load Distinct Fruits, 0 as Price Resident Data;

Concatenate (Data) load * Resident Dummy;

DROP Table Dummy;

good luck

Andrew

[techvarun]

Check the attached qvw.

[Kushal_Chawda]

Data:

load * Inline

[Year, month, Fruits, Price

2017, JAN, Apple, 10

2017, JAN, Banana, 20

2017, JAN, Mango, 30

2017, FEB, Apple, 10

2017, FEB, Banana, 20

2017, FEB, Mango, 30

2017, MAR, Apple, 10

2017, MAR , Banana, 20

2017, MAR , Mango, 30

2018, APR, Apple, 10

2018, APR, PineApple, 15

2018, MAY, Apple, 50

2018, MAY, Banana, 100];

Left Join(Data)

LOAD max(Year) as Year,

     1 as CurYearFlag

Resident Data;

New:

LOAD Distinct month,

              Year

Resident Data

where CurYearFlag=1;

Join(New)

LOAD Distinct Fruits

Resident Data;

Final:

NoConcatenate

LOAD month,

     Year,

     Fruits,

     1 as Flag

Resident New;

DROP Table New;

Left Join(Final)

LOAD month,

     Fruits,

     Price

Resident Data;

Concatenate(Final)

LOAD *

Resident Data

where isnull(CurYearFlag);

DROP Table Data;

create pivot table with expression

=sum({<Flag={1}>}Price)

Note:

Please uncheck suppress zero value option of pivot table

[Kushal_Chawda]

Application is attached in previous post