topic Re: Dynamic Expression changing formulas in QlikView

Dynamic Expression changing formulas

Thu, 27 Mar 2014 17:41:09 GMT

Hi,

I have a formula below. The formula is accurate when my Dynamic Expression is on Day and Hour but not Minute.

count([HTTP Status])/count(total<Hour>[HTTP Status])

I would assume I need to use the formulas below based on my Dynamic Expression (rotates through day, hour, minute)

count([HTTP Status])/count(total<Day>[HTTP Status])

count([HTTP Status])/count(total<Minute>[HTTP Status])

Does anyone know how to make this happen? Change the formula based on the X parameter?

Below is my script.

[Main Data]:

LOAD

filename() as DateHour,

@1 as Remote,

date(date#(mid(@4, 2, 21), 'DD/MMM/YYY:hh:mm:ss'), 'DD MMM YYYY hh:mm:ss') as DateTime,

date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD/MMM/YYYY hh:mm') as CreationDate,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'YYYY')) as Year,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'MMM')) as Month,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD')) as Day,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'hh')) as Hour,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'mm')) as Minute,

replace(@5, ']', '') as TimeZone,

@2 as From

@3 as User

@4 as RequestTime,

@5 as URI,

@6 as [HTTP Status]

FROM

Re: Dynamic Expression changing formulas

Thu, 27 Mar 2014 18:25:47 GMT

Hi there,

Maybe I dont fully understand but can you please elaborate what is 'Change the formula based on the X parameter'.

And how did you determine Minutes is not working in the formula?

Thanks

Re: Dynamic Expression changing formulas

Thu, 27 Mar 2014 18:34:37 GMT

I have a dimension (group) on my X axis. This is filtering between Day, Hour, Minute

from...

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'YYYY')) as Year,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'MMM')) as Month,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'DD')) as Day,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'hh')) as Hour,

Timestamp#(date(date#(mid(@4, 2, 17), 'DD/MMM/YYY:hh:mm'), 'mm')) as Minute,

My data is only one day so I can see the total count of the day, total count by hours, and total count by minutes from using the group expression (dynamically changing my X axis)

My goal is to see the ratio of data in a stacked bar chart.

Example:

Day: 50% Green, 50% Blue

Hour
Hour 1: 25% Green, 75% Blue

Hour 2: 75% Green, 25% Blue

Minute... etc.

When I have my X Axis using Day and Hour, my formula below works.

count([HTTP Status])/count(total<Hour>[HTTP Status])

However, when I rotate to the Minute, it doesn't give me the values I need unless the formula is

count([HTTP Status])/count(total<Minute>[HTTP Status])

So now, I would like to know how I can still rotate through my X axis between my set fields while achieving the formula's I want.

This would be ideal:

Day: count([HTTP Status])/count(total<Day>[HTTP Status])

Hour: count([HTTP Status])/count(total<Hour>[HTTP Status])

Minute: count([HTTP Status])/count(total<Minute>[HTTP Status])

Re: Dynamic Expression changing formulas

Thu, 27 Mar 2014 18:43:16 GMT

You can use the Group name directly in the expression and depending on which field is selected in the group that value will dynamically be put in the epxression.

For example if your group name is My group which has Day,Hours and Minute fields

then the expression will be

count([HTTP Status])/count(total<[My group]>[HTTP Status])

Re: Dynamic Expression changing formulas

Thu, 27 Mar 2014 18:53:27 GMT

Thank you so much. This worked.

I knew I was making it too complicated on myself.

Another quick question, maybe you can help me?

The data I have is in UTC. Is there an easy way to also show another time zone? 7 Hours back?

I would not like to permanently do this but rather have a sort of filter because I want to see the data in more than one time zone.

UTC, CET, PDT, etc.

Re: Dynamic Expression changing formulas

Thu, 27 Mar 2014 19:18:41 GMT

I think

ConvertToLocalTime()

function will work for you. Please search for this in QV Help. It has very good explanation.

eg: ConvertToLoaclTime(Field,'Paris')

ConvertToLoaclTime(Field, ’GMT-05:00’)