Adding Values based on Condition

[Anonymous]

HI All,

Need a quick help. In the attached excel I need to make Column D in qliksense.

Logic is if the city is Delhi then add customer count of  Mix Customers, Non Sales Customers and sale customers which falls under Delhi city.

Same logic is for other city.

Kindly help.

Regards,

Avinash

[YoussefBelloum]

Hi,

so you want 3 different measures, separated counts for each country ?

[Anonymous]

Correct

[Anonymous]

The formatting also should be same as shown in excel.

[YoussefBelloum]

Maybe like this ?

test:

LOAD storelocation,

    customer_category,

    customer_count,

    if(storelocation='Delhi',customer_count) as count_Delhi,

          if(storelocation='Mumbai',customer_count) as count_Mumbai,

                    if(storelocation='Chennai',customer_count) as count_Chennai

...

find attached the QVW

[OmarBenSalem]

You want to show in a table or to be created within the script?

If you want to show it in a table, you just add an expression as follow:

sum(total <storelocation>customer_count)

[OmarBenSalem]

if you want to calculate it within the script:

location:

load * Inline [

location, category, count

Delhi, "Mix customers", 45

Delhi, "Non-Sale customers", 465

Delhi, "Sale customers", 454

Mumbai, "Mix customers", 1451

Mumbai, "Non-Sale customers", 1445

Mumbai, "Sale customers", 14

Chennai, "Mix customers", 2445

Chennai ,"Non-Sale customers", 21

Chennai ,"Sale customers", 54

];

left join (location)

load location, sum(count) as total Resident location Group by location;

result:

[Ricardo_Gerhard]

Just load the columns storelocation, customer_category, customer_count and create a Table with the expresion sum(customer_count) with the dimension Storelocation.

[Lucke_Hallbergson]

import first 3 columns and create pivot table, then aggregations on location and categories are easily possible;