topic Re: Ignoring dimension in a chart in QlikView

Ignoring dimension in a chart

Wed, 07 Nov 2012 08:42:23 GMT

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,

Re: Ignoring dimension in a chart

Wed, 07 Nov 2012 08:48:48 GMT

Hi,

can you post a .qvw sample of problem?

Re: Ignoring dimension in a chart

sujeetsingh — Wed, 07 Nov 2012 08:52:37 GMT

Yes just give a sample copy....it will be good.

Re: Ignoring dimension in a chart

Wed, 07 Nov 2012 08:56:20 GMT

Is there a quick How-to to make an example? My dataset is huge and on a remote secure server. I would not know how to recreate something like it in excell and make a qvw out of it...

So there is no quick set expression trick like: "UseNumericValue()" or anything like that?

Re: Ignoring dimension in a chart

Wed, 07 Nov 2012 10:44:55 GMT

The yellow and the green line have to become straight horizontal lines. The formula from yellow is ([usage till today] / [days till today]) * [days of the month]

The problem is that it uses the dimension when i do not want it to. Normally the expression would return one value, now it returns values for each different day..

Re: Ignoring dimension in a chart

Wed, 07 Nov 2012 13:27:56 GMT

You can try with Set Analysis:

Yellow Expression:

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

Hope this helps

Regards

Re: Ignoring dimension in a chart

Wed, 07 Nov 2012 13:35:40 GMT

Thank you mario,

but this doesn't work, using "cdr_startday=" causes to ignore selections in the cdr_startday, not the dimension of the graph.

Can it be so difficult? if i calculate the value in a table without dimension cdr_startday I get a value. If i type this value in the chart expressions there is a straight line...

Re: Ignoring dimension in a chart

Wed, 07 Nov 2012 13:53:28 GMT

Try to create 2 graphs and overlay them , with transparent background for the second

Fisrt graph=Dimension,Blue Line Expression

Second Graph = without dimension,Yellow Line Expression,Green Line Expression

All with your formulas,without Set Analysis

Re: Ignoring dimension in a chart

Wed, 07 Nov 2012 15:10:11 GMT

Thanks Mario,

I tried to avoid that because at some point it will be messed up (selections, dragging, changing axis). But i guess i could try it..

Still a other solution is welcome!

Re: Ignoring dimension in a chart

jagan — Wed, 07 Nov 2012 15:54:45 GMT

Hi,

Try

Suppose if you have Dimension1, Dimension2 then you can ignore Dimension2 by using

=Sum(TOTAL<Dimension2> Sales)

Regards,

Jagan.

Re: Ignoring dimension in a chart

whiteline — Wed, 07 Nov 2012 20:14:23 GMT

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])

Re: Ignoring dimension in a chart

Thu, 08 Nov 2012 07:48:36 GMT

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:)

Re: Ignoring dimension in a chart

Thu, 08 Nov 2012 08:20:35 GMT

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?

Re: Ignoring dimension in a chart

whiteline — Thu, 08 Nov 2012 08:52:08 GMT

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.

Re: Ignoring dimension in a chart

Thu, 08 Nov 2012 09:13:11 GMT

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.

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?

Re: Ignoring dimension in a chart

whiteline — Thu, 08 Nov 2012 09:36:45 GMT

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.

Re: Ignoring dimension in a chart

Thu, 08 Nov 2012 10:00:11 GMT

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)

Re: Ignoring dimension in a chart

whiteline — Thu, 08 Nov 2012 12:06:19 GMT

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)

Re: Ignoring dimension in a chart

Thu, 08 Nov 2012 12:26:40 GMT

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>?)

Re: Ignoring dimension in a chart

whiteline — Thu, 08 Nov 2012 16:08:00 GMT

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).