Forum Discussion

Chanleakna123's avatar
Chanleakna123
Post Prodigy
7 years ago
Solved

Count 13 Columns

HI All , I have W1 W2 until W13 , 

and i wanna Count W1 until W13 , 

it is like we count the Column, In Column 

as example below :

Customer Name BRAK KIMSY , Total Count : 4 

Customer Name PTEAS MAE RESTAURANT , Total Count 2 

Do you have any idea how to settle this ? I have explored for 4 hours , and no result. 

 

Show and Tell
  • ZunzunUOC's avatar
    ZunzunUOC
    7 years ago

    In theory, your code is correct. Is a measure or a calculated column?

     

    Ok, the problem is custumer name has several rows, is that possible? Do you want a new table just a custumber name and count?

     

    In the lookupvalue code, you need to add as many search  column name as columns you have:

     

    Total Count = 
var _UserName= CountColumns[Customer Name]
var _Flavour= CountColumns[Flavour]
return
IF(LOOKUPVALUE(CountColumns[W1];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W2];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W3];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W4];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W5];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W6];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)

3 Replies

  • ZunzunUOC's avatar
    ZunzunUOC
    Resolver III
    7 years ago

    Hi Chanleakna123 , maybe there are better solutions (a loop would be great), but this works, is a calculated column:

     

    Total Count = 
var _UserName= CountColumns[Customer Name]
return
IF(LOOKUPVALUE(CountColumns[W1];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W2];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W3];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W4];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W5];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W6];CountColumns[Customer Name];_UserName)>0;1;0)

    I have simplified with just 6 columns.

     

    The result:

     

    Best Regards,
    Miguel

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Chanleakna123's avatar
      Chanleakna123
      Post Prodigy
      7 years ago

       

      hi ZunzunUOC 

      there was a message pop up as per below. 

      can you help me to acheive this ? 

      this is my first try with 2 week only , and the message turn out as per below

      • ZunzunUOC's avatar
        ZunzunUOC
        Resolver III
        7 years ago

        In theory, your code is correct. Is a measure or a calculated column?

         

        Ok, the problem is custumer name has several rows, is that possible? Do you want a new table just a custumber name and count?

         

        In the lookupvalue code, you need to add as many search  column name as columns you have:

         

        Total Count = 
var _UserName= CountColumns[Customer Name]
var _Flavour= CountColumns[Flavour]
return
IF(LOOKUPVALUE(CountColumns[W1];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W2];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W3];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W4];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W5];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W6];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)