topic gauge problem in QlikView

gauge problem

Thu, 02 Aug 2012 11:48:54 GMT

Dear community,

I have a problem creating a gauge using data in a specific set

The gauge is ment to display a percentage of used data in a month

I need it to allways display data in the current month and year, no matter what the selection.

The data is displayed in cdr_data_in_kb and it is separated in different categories. I only need it to display data in the category DBS1, the category is named dataservice_type

so far i created this analysis

=avg({$<dataservice_type={$(='dbs1')}>}cdr_data_in_kb)

It does not display the correct data when i select the month and the year mannualy and it does not change when i select no month or year (which it should)

Does anyone know what i am doing wrong?

Bonusquestion; how do i show the data in MB in stead of KB? /1024 does not seem to work with the gauges

Thank you for your help!

Re: gauge problem

kaushiknsolanki — Thu, 02 Aug 2012 12:02:06 GMT

HI,

What i understood is you want to have a gauge chart which shows you the percentage usage of the category DBS1 in comparision with over all usage. And that too only for current Month and Year. If yes then try this expression in guage chart.

Sum({<dataservice_type = {"dbs1"},Month = {"$(=Max(Month))"},Year = {"$(=Max(Year))"}>}cdr_data_in_kb)

Sum({<Month = {"$(=Max(Month))"},Year = {"$(=Max(Year))"}>}cdr_data_in_kb)

And go to Number tab of the chart properties and select the show in percentage option.

Bonus Answer. When you are taking the percentage it doesnt matter whether the data is in KB or in MB. For example If I have 10240 KB / 1024000 KB will give me 10% and same if i Use in MB 10 MB / 100 MB will also give me 10%.

Hope this will help.

Regards,

Kaushik Solanki

Re: gauge problem

Thu, 02 Aug 2012 12:07:30 GMT

Hi Kaushik,

Thank you for your answer but i need the gauge to show the amount of data used in the DBS1 category compared by the maximum availablein the DBS1 (max available would be bundle_size_mb * amount of DBS1 connections in database)

the amount of data used in MB on average

i am guessing the AVG function is a better solution, but i do not know for sure

Re: gauge problem

kaushiknsolanki — Thu, 02 Aug 2012 12:10:32 GMT

Hi,

If possible pls give us some exaple of your data so that we can help you to get what you exactly looking for.

Regards,

Kaushik Solanki

Re: gauge problem

Thu, 02 Aug 2012 12:23:32 GMT

for example

i have different categories of data users

one of those categories is DBS1

they all use different amounts of MB's but users in this category cannot exeed an average of 100MB

so i need the total amount of MB's used divided by the amount of users in the category

MB's used = "cdr_data_in_kb"

"DBS1" users are in category "dataservice_type"

see attached file of what i want the function to do, i did it in excel, i hope thats okay!

Re: gauge problem

Thu, 02 Aug 2012 12:26:29 GMT

maybe this one is better

Re: gauge problem

kaushiknsolanki — Thu, 02 Aug 2012 12:38:42 GMT

Hi,

You can use this expression for the excel sheet you have given.

Sum({<Month = {"$(=Max(Month))"},Year = {"$(=Max(Year))"} >}cdr_data_in_kb)

/ count({<Month = {"$(=Max(Month))"},Year = {"$(=Max(Year))"},dataservice_type = {"dbs1"}>}distinct user)

This will give you the desired output for the current month Year.

Regards,

Kaushik Solanki

Re: gauge problem

Thu, 02 Aug 2012 12:52:17 GMT

Sum({<Month = {"$(=Max(Month))"},Year = {"$(=Max(Year))"} >}cdr_data_in_kb)

/ count({<Month = {"$(=Max(Month))"},Year = {"$(=Max(Year))"},dataservice_type = {"dbs1"}>}distinct user)

the first bold part of the syntax still needs the limitation to the dataservice_type right?

Re: gauge problem

kaushiknsolanki — Thu, 02 Aug 2012 12:57:50 GMT

Yes sorry I missed Try this updated expression.

Sum({<Month = {"$(=Max(Month))"},Year = {"$(=Max(Year))"}, dataservice_type = {"dbs1"}>}cdr_data_in_kb)

/ count({<Month = {"$(=Max(Month))"},Year = {"$(=Max(Year))"},dataservice_type = {"dbs1"}>}distinct user)

Regards,

Kaushik Solanki

Re: gauge problem

Thu, 02 Aug 2012 13:06:40 GMT

it does seem to work!

now the only problem is, i think, a database problem. The syntax gives me a different answer then when i try

avg({<Month =
{"$(=Max(Month))"},Year =
{"$(=Max(Year))"},dataservice_type = {"dbs1"}>}cdr_data_in_kb)

or when i divide the amount of data used with the amount of users i have in a table.

is there a way to disable the "distinct user" part of the syntax? if this gives a other result we now we have duplicates in the databases

Re: gauge problem

Thu, 02 Aug 2012 13:14:46 GMT

Nevermind i allready found the problem!

Thank you very much for your help kaushik!