topic Values of a Textbox shown in Diagramm in QlikView

Values of a Textbox shown in Diagramm

Frank_Hartmann — Mon, 24 Nov 2014 11:43:01 GMT

Hello QV Community,

please have a look at my qwv file.

I need the monthly values of a textbox to be displayed within a chart (with last 12 Month dimension)!

Or is it possible, that my Datastructure does not fit to my requirement (Do i have to transform the table in script for getting aggregated values?)

Thanks for your help

kind regards

Re: Values of a Textbox shown in Diagramm

Frank_Hartmann — Thu, 27 Nov 2014 12:17:15 GMT

Any ideas?

Re: Values of a Textbox shown in Diagramm

salto — Thu, 27 Nov 2014 12:39:33 GMT

Hi Frank,

Please change the dimension of the chart to this calculated dimension:

=if(date(monthstart(CalendarYearMonthDay))>date(monthstart(addmonths(today(),-12))),CalendarYearMonth)

What I have not understood is the expression. What exactly do you need with the daten_Vorgang field?

I have summed it:

But maybe this is not the expression you need. Just let me know!

Re: Re: Values of a Textbox shown in Diagramm

Frank_Hartmann — Thu, 27 Nov 2014 13:59:17 GMT

Hi SALTO,

thx for your reply.

I try to describe my problem more precisely. I have a excelfile with 3 columns.

First column is the date. Second column the name of the workshop and the third one (Daten_Vorgang) describes how often the workshop tried to access on our servers. The original data gives me the values by calenderweek, but i need it by month, so i connected my table with my mastercalendar.

Next step was to create a tablediagramm with the workshops as dimension.

As basic expressions I calculate :

1.) the actual monthvalues over the workshops:

=sum({$<MonthDiff={[$(vAktuellerMonat)]}>}Daten_Vorgang)

2.) the monthvalue of the month before actual month

=sum({$<MonthDiff={[$(vZeitraum2Monate)]}>}Daten_Vorgang)

3.) The monthvalue 3 month ago:

=sum({$<MonthDiff={[$(vZeitraum3Monate)]}>}Daten_Vorgang)

These expressions above I use in a fourth expression for calculating the 3 month average:

4.) (sum({$<MonthDiff={[$(vAktuellerMonat)]}>}Daten_Vorgang)+

sum({$<MonthDiff={[$(vZeitraum2Monate)]}>}Daten_Vorgang)+

sum({$<MonthDiff={[$(vZeitraum3Monate)]}>}Daten_Vorgang))/3

5.) The next Expression evaluates if the actual monthvalue is greater than the average expression in (4.)

If((sum({$<MonthDiff={[$(vAktuellerMonat)]}>Daten_Vorgang)>

sum({$<MonthDiff={[$(vAktuellerMonat)]}>Daten_Vorgang)+

sum({$<MonthDiff={[$(vZeitraum2Monate)]}>}Daten_Vorgang)+

sum({$<MonthDiff={[$(vZeitraum3Monate)]}>}Daten_Vorgang))/3,1,0)

6.) The last expression just gives me the total numbers of distinct workshops accessing the server up to now :

Count(distinct(WS_Vorgang)

For 5.) and 6.) I see a "1" in the diagramm if the condition is fullfilled otherwise "0". To see the TOTAL Value of the 1´s over all workshops, I activated the corresponding option in the diagrammsettings.

With These total values i want to create a new diagramm with a quote per month: Total value of 5.) / Total value of.6)

But no formula i am trying works untill now

The requirement is to show these quotes in a 12 month diagramm.

Hope its more clear now!

Any ideas?

Re: Re: Values of a Textbox shown in Diagramm

salto — Thu, 27 Nov 2014 14:29:54 GMT

Hi,

"Total value of 5.) / Total value of.6) " OR "Total value of 4.) / Total value of.6) "?

5) return 1 or 0, that is why I am asking...

Re: Re: Values of a Textbox shown in Diagramm

salto — Thu, 27 Nov 2014 14:39:42 GMT

Sorry again Frank,

What exactly is the quote per month: the total number of "Daten_Vorgang" that month divided per the number of Workshops that have contacted the servers, that month?

Re: Re: Re: Values of a Textbox shown in Diagramm

Frank_Hartmann — Thu, 27 Nov 2014 15:39:43 GMT

Hi Salto,

when you open my qvw and put the slider on the top to -1 (october) then you see the two values i marked in red on the attachment.

The left value divided by the right value should give me 100% (9/9) Quote for this month. If you put the slider to -2 (september)

then the values for this previous month should be 88,88% (8/9). And these quotes i want to show in a 12 month chart and later also showing the moving 12month avg of theses quotes !

thx for your help