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

Ignoring dimension in a chart

I have a problem with a chart that i build.

The chart has a line which monitors usage per day and accumalates this, so it is a (mostly) linear line upwards.

I also have a formula which calculates the expected endpoint of this line so usage this month till today, defided by the amount of days this month till today, multiplied by the number of days in the month.

third I have a formula which calculates the amount of data which is available to use. This is amount of users times the amount the user can use.

These last two formulas are not within the dimension (not linked to days) and therefore does not display correctly. I would like to know if there is a adject for the expression which calculates the formula and uses only the result in the chart.

If i use just a number as the expression, for instance 1203, it creates astraight line in the chart. So i want the expression to do just that, calculate the number and place it as a straight line.

Kind regards,

19 Replies
whiteline
Master II
Master II

Hi.

To discard all dimensions except Cdr_StartDay, try:

=sum(total<Cdr_StartDay> [usage till today]) / sum(total<Cdr_StartDay> [days till today])) * sum(total<Cdr_StartDay> [days of the month])

Not applicable
Author

I know, but the intention is to ignore the dimension of the graph-> Cdr_StartDay. Not to ignore the others like you suggest. Thanks anyway:)

Not applicable
Author

Ok, ive looked some more in the TOTAL function and it looks like it might be a solution. But I cannot get it to work.

for [usage till today] I use       =sum({<DataServiceTypeFull={"DBS1S"}>} Cdr_DataKB)/1024

for [days till today] I use         =count(distinct Cdr_StartDay)

for [days of the month] I use  =avg(Cdr_StartMonthDays)

The expression in a bar chart I use is

=(sum({<DataServiceTypeFull={"DBS1S"}>} Cdr_DataKB))/1024 /(count(distinct Cdr_StartDay)))*avg(Cdr_StartMonthDays)

This works but it has DataServiceTypeFull as the dimension and no days.

The expression you suggest would be

=((sum(total<Cdr_StartDay> (sum({<DataServiceTypeFull={"DBS1S"}>}Cdr_DataKB)/1024)) / sum(total<Cdr_StartDay> count(distinct Cdr_StartDay)))*sum(total<Cdr_StartDay> avg(Cdr_StartMonthDays)))

This expression does not return any data.. do you have an idea why?

whiteline
Master II
Master II

I don't see the names of other dimensions on screenshots

I thought you read the help and substitute the correct dimension names

You don't have to build monstrous expression using the above on top of yours.

I suggested ir as an example as you've mentiond earlier: [usage till today] / [days till today]) * [days of the month]

Take your expression, modify the aggregation functions with a total modifier and correct dimensions.

Not applicable
Author

you said it would discard all dimensions except Cdr_StartDay, i want it to discard Cdr_Startday.

I try to work with the total, but i've never used it before and do not totally understand it..

I feel like i am not able to explain my problem

The graph is showing the total data usage from our clientbase till today day (blue)

&

The prediction of how much data they've used at the end of the month (yellow)

The problem with the yellow expression is that it has different values per day, as the data usage per day varies. The only usable data is the prediction from today, the graph shows predictions for each single day(because the day is the dimension).

The yellow line has to become a straight horizontal line, since the prediction for today is a single value.

QV community2.png

This is how i want the graph to be, so that we can manage availability and usage.

If I enter 250.000 MB it gives me the straight line i want. Now i want the graph to use the outcome of the expression (250.000) and not the outcome per day(i.e. ignore the dimension).

A possible solution is to refer to a cell in another table, would this be possible?

whiteline
Master II
Master II

Could you create a table without Cdr_Startday dimension and calculate the desiered values ?

If so, post the corresponding expressions for prediction and availabale value.

Not applicable
Author

QV community3.png

Total usage:                          =sum(Cdr_DataKB)/1024

Average usage per day:                   =(column(1))/count(DISTINCT Cdr_StartDay)

prediction                                          =(column(2))*avg(monthdays)

Column(3) = the yellow expression from before. This calculated value i want as a constant in the graph, this constant changes only on reload (daily). As you can see without dimension Cdr_StartDay it does the trick. So i want the graph to ignore the dimension for the last expression (prediction)

whiteline
Master II
Master II

So as you've used DataServiceType as dimmension use it in your expressions for line chart:

=sum(total<DataServiceType> Cdr_DataKB)/1024/count(DISTINCT total<DataServiceType> Cdr_StartDay)

=sum(total<DataServiceType> Cdr_DataKB)/1024/count(DISTINCT total<DataServiceType> Cdr_StartDay)*avg(total<DataServiceType> monthdays)

Not applicable
Author

Finally there is a straight line!

Now the million dollar question, can i still use set analysis in this expression. As you can see in the table there are different names in the DataServiceTypeFull. I need the graph only to be for DBS1S. So somewhere we need to add {<DataServiceTypeFull={"DBS1S"}>} ?

Now it just adds up all the data (which I believe is the function of <total>?)

whiteline
Master II
Master II

Yes, of course you can.

I suggest you to consider also Trellis function of QV (so that you can tile your graphs for different DataServiceTypeFull values).