4 Replies Latest reply: Apr 26, 2016 7:26 AM by Sangram Reddy RSS

    Butterfly chart with client age etc

    Kalmer Kaljuste

      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