Butterfly chart with client age etc

[Kalmer]

Hello!

I've looked into it and found quite a few solutions for it. Now i have done everything what Qlik community has suggested but when i check the results in MySQL i get a different result. FOR ME right now, it seems that my IF measure expression is not working right. I would love to have some help in here (will also upload the project).

I've done the data load with a fewer amount of fields (since these are my clients).
So for my butterfly Bar chart i use this Dimension:

dual(

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=71, '71 and older',

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=61, '61-70y old',

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=51, '51-60y old',

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=41, '41-50y old',

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=31, '31-40y old',

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=1, '18-30y old', 'No info'))))))

,

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=71, 1,

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=61, 2,

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=51, 3,

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=41, 4,

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=31, 5,

if(aggr(sum({$<customer_class={'KLUBI'}, customer_name-={'SERK 2007', 'Tartu Ülikooli', 'Rahva Raamat'}>}(Age(Today(),Member_Age_stats))), customer_ID)>=1, 6, 7)))))))

And 2 measures since my IF statement ain't working right (qlicking right.. )

if(Member_Sex_stats='mees', (count(distinct{$<Member_Sex_stats={'naine'}, Member_Age_stats={'*'}>}customer_ID)), (count(distinct{$<Member_Sex_stats={'mees'}, Member_Age_stats={'*'}>}customer_ID)*(-1)))

(Well it should show + and a - result... does only 1).'

if(Member_Sex_stats='naine', (count(distinct{$<Member_Sex_stats={'mees'}, Member_Age_stats={'*'}>}customer_ID)), (count(distinct{$<Member_Sex_stats={'naine'}, Member_Age_stats={'*'}>}customer_ID)))

So pretty much all the help in the world is welcome. Thank you in advance!
PS "naine" = woman, "mees" = man

[reddy-s · Accepted solution]

Hi Kalmer,

Here it is:

I have created an example and check the link where I have addressed a similar problem.

Butterfly or Tornado Chart | Qlik Community

Thanks and Regards,

Sangram Reddy.

[Kalmer]

Hello, This example could not solve my problem.
I even changed it so that i had 2 dimension (DUAL for counting the age of my clients) + the sex and the measureing is done for the client sex. I couldn't get it work like that (i've tried it already once). If you have any other suggestions please let me know (Maybe i should calculate people age in the load before i use the app, but in my opinion it will be too much hassle since i have like 200 clients and this is only one of my clients). What i mean is like "when i get a new row (data) of a client i just add it to the database and also when a client changes something only one row of data will update since "last update" field was updated. But now if i would have the birthday before load i would have to do the load every day since i need to reload the clients daily.

Anyway any sort of help is still appreciated.

[Kalmer]

managed to fix the butterfly chart. The statement which i had was correct. but thanks

[reddy-s]

Always Welcome!