Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

gauge problem

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!

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi

   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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

10 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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)

or

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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!

Not applicable
Author

maybe this one is better

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

   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?

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi

   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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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