topic Create a Chart using calculated item in QlikView

Create a Chart using calculated item

Wed, 09 Feb 2011 10:46:10 GMT

Dear all,

Is it possible to create a chart to count how many times appear of a calculated item? I tried but unable to do so.

For example:

Any suggestion?

Thanks

Create a Chart using calculated item

deepakk — Wed, 09 Feb 2011 10:56:16 GMT

hi,

Yes its possible. ONe of the way of doing it is creating a field at script level.

if((count) <= 5 , 'Low usage' ,if(count > 5 and count < 20 'Mid USage', if(count >20, 'High Usgae'))) as Calculateditem,

Now in Fron end add the two dimension COuntry and Calculateditem and in expression count( Calculateditem ).

I hope this helps.

Create a Chart using calculated item

Wed, 09 Feb 2011 11:06:27 GMT

Hi Deepak,

Thanks for your suggestion. But I do not want this to put into the loading scripts because the data consists of 2 years data in transaction level and thus, the number of count is also a calculated item as well. In QV, will have a selection page for users to choose whether they want to see the monthly, bi-monthly, quarterly, yearly, etc. Hence, pre-calculate in the scripts does not work.

I want the chart can be dynamically change based on the users' choice in selection page.

Thanks

Create a Chart using calculated item

deepakk — Wed, 09 Feb 2011 12:18:46 GMT

hi,

PFA.

I hope the attachment helps you out.

Create a Chart using calculated item

Thu, 10 Feb 2011 04:22:21 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7823.Test222.zip:550:0]

Hi Deepak,

Thanks a lot and it works.

Moreover, I added more data, country field as well, to your qvw file and plot a chart as attached.

I found that since there are too much values on the x-asis, the chart is unable to show all the country and thus, it hided some of them. I would like to ask

1) You can see that even there is no staff in AU for Prod A, but the chart still having AU in A. Can I remove all the value in X-asis which is not exists to the specific Prod. For example, I just want to show CN, ID and IN for Prod D.

2) If 1 cannot be done, then will face another problem is not all the countries are shown in the chart. For example, CN label in x-asis is missing but the CN bar is there. This will confuse users. What should have do in order to show all?

Thanks

Create a Chart using calculated item

deepakk — Thu, 10 Feb 2011 09:44:09 GMT

hi,

I saw the application , I am not able to figure out the first problem. The second one can be resolved by reducing the font size of to X size.

Create a Chart using calculated item

Thu, 10 Feb 2011 19:42:51 GMT

I don't think there is an option to hide the Country's with no Counts. What you could do is create a Calculated Dimension as '=Prod & Country'. This will collapse the grouping into a single layer and will chart appropriately. A (possible) downside is the unfriendly labels.

Create a Chart using calculated item

Fri, 11 Feb 2011 02:46:42 GMT

Thanks Jamie.

That's no good. But it is weird that in pivot format, it shows in a perfect way while in chart is not.

I think QV has to look at this.