topic Re: Show Max Total value and breakdown value in QlikView

Show Max Total value and breakdown value

beunderf — Sat, 14 May 2011 13:05:47 GMT

Dear all,

Please look at attached file.

I have a table that shows values per time period (per day). It shows total values and values per machine.

Now I want to have Bar Chart that shows me the MAX(TOTAL) values per time period, but also a break down of this total value.

Does anyone know how to do this?

Thanks,

Frank

Show Max Total value and breakdown value

Sat, 14 May 2011 14:53:05 GMT

Hi,

Could give more us info. Not able to understand yor question.

- Sridhar

Re: Show Max Total value and breakdown value

beunderf — Sat, 14 May 2011 17:40:09 GMT

I will try explain more.

I have a table with the following information:

PERIOD TOTAL DATE MACHINE1 MACHINE2 MACHINE3

1015-1029 20 13-02-2011 10 10 0

1015-1029 10 12-02-2011 2 2 8

1030-1044 15 12-02-2011 4 5 6

1030-1044 4 13-02-2011 0 2 2

Now I want to show a Stacked bar with dimension PERIOD. The Stacked bar should show the machine1, 2 and 3 value where the Total is the highest for the period.

In my Example it should show for period '1015-1029' the values MACHINE1=10, MACHINE2=10, MACHINE3=0 , because for period '1015-1029' TOTAL=20 is the highest value

For Period '1030-1044' it should show MACHINE1=4, MACHINE2=5, MACHINE3=6

Does this make clear what I am looking for?

Thanks in advance

Re: Show Max Total value and breakdown value

beunderf — Sun, 15 May 2011 08:14:35 GMT

Anyone?? Please, who can help me 😞

Re: Show Max Total value and breakdown value

Sun, 15 May 2011 11:20:44 GMT

Hi Frank,

Please find attached a solution for this. You need to create another table with max(TOTAL) for each PERIOD like below.

newdata:

load PERIOD,max(TOTAL) as MaxTOTAL resident Max_Values group by PERIOD;

And then use the newly created variable MaxTOTAL in chart expression like below

sum(if(MaxTOTAL=TOTAL,MACHINE1)) for MACHINE1

sum(if(MaxTOTAL=TOTAL,MACHINE2)) for MACHINE2

sum(if(MaxTOTAL=TOTAL,MACHINE3)) for MACHINE3

Find attached the modified QVW file.

Best Regards,

Sajeevan

Re: Show Max Total value and breakdown value

beunderf — Sun, 15 May 2011 12:03:19 GMT

Hi Sajeevan,

Thanks for your anwser. But it is not completly a solution for my problem.

With your solution I lose the flexiblity to select the DATES for which I want to analyze the MAX values.

If I select 12-02-2011 in your example, I loose the information for one PERIOD. I want to be flexible in the dates to select. If I select no DATES your solution is correct.

So if I select 12-02-2011 I want to see the max values for that date, if I select 13-02-2011 I want to see the max values for that date, if select both dates, I want to see the max values within the two dates.

Do you understand my problem?

Re: Show Max Total value and breakdown value

Sun, 15 May 2011 14:31:30 GMT

Hi Frank,

Your requirements were not very clear from the earlier post. Thanks for making it more clear now. I just reworked on the solution with a different approach. Rather creating an extra table I am trying to resolve it through the expression itself. Please find attached the modified one. Check the chart expression. I am not sure whether this will meet 100% of your requirements but sure you will be able to understand how it works and needs to be find tuned.

Best Regards,

Sajeevan