topic Re: Pivot Chart rendering error in QlikView

Pivot Chart rendering error

prat1507 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Pivot Chart rendering

sunny_talwar — Thu, 10 Aug 2017 18:42:45 GMT

Is this already resolved?

Re: Pivot Chart rendering

prat1507 — Thu, 10 Aug 2017 18:45:49 GMT

No, I remember that was a different question with No selection.

Regards

Pratyush

Re: Pivot Chart rendering

sunny_talwar — Thu, 10 Aug 2017 18:47:24 GMT

No the reason I asked it is because you have marked this as assumed answer... I was not sure if you got what you wanted or are still looking for help

Re: Pivot Chart rendering

prat1507 — Thu, 10 Aug 2017 18:51:35 GMT

No I am still looking for help, I dont know how did I mark it. I am travelling right now and maybe it happened due to mishandling of my phone.

Re: Pivot Chart rendering error

prat1507 — Thu, 10 Aug 2017 18:55:18 GMT

How do I unmark it to an open question?

Re: Pivot Chart rendering

sunny_talwar — Thu, 10 Aug 2017 18:58:03 GMT

May be this

=Sum({<Fruits = p({<Year = {2018}>}Fruits)>}Price)

Re: Pivot Chart rendering error

prat1507 — Thu, 10 Aug 2017 19:00:45 GMT

Hi Sunny,

So I need all the Fruits to Show in the pivot table but prices only for the current FY.

Regards

Pratyush

Re: Pivot Chart rendering error

sunny_talwar — Thu, 10 Aug 2017 19:02:07 GMT

Like this?

Re: Pivot Chart rendering error

prat1507 — Thu, 10 Aug 2017 19:22:16 GMT

Something like this

Fruits Month Price

Apple Apr 10

Banana May 100

Mango Apr 0

PineApple APR 50

Apple May 15

Mango May 0

Banana APR 0

PineApple May 0

Re: Pivot Chart rendering error

sunny_talwar — Thu, 10 Aug 2017 19:35:46 GMT

But Mango is not even there in Apr-2018?

Re: Pivot Chart rendering error

prat1507 — Fri, 11 Aug 2017 05:09:03 GMT

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

Re: Pivot Chart rendering error

prat1507 — Fri, 11 Aug 2017 06:20:28 GMT

Can anyone help please, I need it urgently.

Regards
Pratyush

Re: Pivot Chart rendering error

effinty2112 — Fri, 11 Aug 2017 09:24:13 GMT

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

Re: Pivot Chart rendering error

techvarun — Fri, 11 Aug 2017 09:29:41 GMT

Check the attached qvw.

Re: Pivot Chart rendering error

Kushal_Chawda — Fri, 11 Aug 2017 10:07:08 GMT

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

Re: Pivot Chart rendering error

Kushal_Chawda — Fri, 11 Aug 2017 13:29:24 GMT

Application is attached in previous post