topic Re: Expression problems in chart in QlikView

Expression problems in chart

imallears — Wed, 25 Nov 2020 16:16:04 GMT

I have two charts, one hidden on a conditional show.

Dimensions for both charts are:

Week/Year (This is also the default vie when app is opened)

Name

Day

Expression for Chart A:

if(avg({$<FlagA={'1'},FlagB={'1'}>} HoursIn8HourDay/8)>1,1,
avg({$<FlagA={'1'},FlagB={'1'}>} HoursIn8HourDay/8))

Expression for Chart B: (Hidden. It has a conditional show if Name is selected)

if(sum({$<FlagA ={'1'}, FlagB ={'1'}>} HoursIn8HourDay/8)>1,1,
sum({$<FlagA ={'1'}, FlagB ={'1'}>} HoursIn8HourDay/8))

Listboxes are:

Year, Month, Week, Day and Name

The chart works if selections are made in the order - Year, Month, Week, Name.

If Name is chosen first however the chart is incorrect (as it’s using the sum expression )

Is a hidden chart even required or will one chart do with multiple if statements in an expression?

Thanks in advance!

Re: Expression problems in chart

sunny_talwar — Sat, 02 Apr 2016 14:45:26 GMT

You can create a single chart with if condition such as

If(GetSelectedCount(Name) > 0,

if(sum({$<FlagA ={'1'}, FlagB ={'1'}>} HoursIn8HourDay/8)>1,1,
sum({$<FlagA ={'1'}, FlagB ={'1'}>} HoursIn8HourDay/8)),

if(avg({$<FlagA={'1'},FlagB={'1'}>} HoursIn8HourDay/8)>1,1,
avg({$<FlagA={'1'},FlagB={'1'}>} HoursIn8HourDay/8)))

But this is beside what the issue is.

Re: Expression problems in chart

Kushal_Chawda — Sat, 02 Apr 2016 14:48:49 GMT

I don't think you required two chart. One can do the trick

Re: Expression problems in chart

imallears — Sat, 02 Apr 2016 14:54:59 GMT

Unfortunately that creates the same issue.

When Name is selected first, the default chart shows sum rather than average

Re: Expression problems in chart

sunny_talwar — Sat, 02 Apr 2016 18:20:09 GMT

Do you only want the Sum expression when all of the 4 fields have at least one selected value? May be this:

If(GetSelectedCount(Name) > 0 and GetSelectedCount(Year) > 0 and GetSelectedCount(Month) > 0 and GetSelectedCount(Week) > 0 and GetSelectedCount(Day) > 0,

if(sum({$<FlagA ={'1'}, FlagB ={'1'}>} HoursIn8HourDay/8)>1,1,
sum({$<FlagA ={'1'}, FlagB ={'1'}>} HoursIn8HourDay/8)),

if(avg({$<FlagA={'1'},FlagB={'1'}>} HoursIn8HourDay/8)>1,1,
avg({$<FlagA={'1'},FlagB={'1'}>} HoursIn8HourDay/8)))

Re: Expression problems in chart

imallears — Sat, 02 Apr 2016 22:42:22 GMT

Thanks, almost have it now.

The calculation for week is incorrect. It should be sum but is showing as average.

Re: Expression problems in chart

sunny_talwar — Sun, 03 Apr 2016 10:51:57 GMT

Not sure I understand, may be you can explain me the logic behind what selections show expression1 and what selections show expression2

Re: Expression problems in chart

imallears — Sun, 03 Apr 2016 21:33:42 GMT

When there is one Flag A my daily % calculation is correct

When there are two Flag A's my daily Log % total is incorrectly divided by 2

When there are three Flag A's my daily Log % total is incorrectly divided by 3

and so on

Re: Expression problems in chart

sunny_talwar — Mon, 04 Apr 2016 09:58:38 GMT

It is difficult to understand what you are trying to do. Do you have a sample you can share with your expected output?