Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

marishnagendran
Contributor

Straight table

Dear All,

 

BranchCustomerTargetAchieved
    
Branch 1Customer 110050
Branch 1Customer 210020
Branch 1Customer 3020
Branch 1Customer 4040
Branch 2Customer 14020
Branch 2Customer 5050

 

A above is my transaction table

Output is

Branch TargetAchievedWithout Target Achieved
    
Branch 120013060
Branch 2407050

 

If target is 0 for the customer then in

Without target achieved column gets calculated. Is it possible????

1 Solution

Accepted Solutions
MVP
MVP

Re: Straight table

Max fiscal year and max month can't really return an amount... so it doesn't seem to be the full expression... 

Since you're not giving a complete expression I can only hint in which direction you have to try.... something like this:

Sum( Aggr( If( <target-expression> =0,  <amount-expression> ) , dim1 , dim2 , ... )

10 Replies
MVP
MVP

Re: Straight table

[ TIP: to show any contributor that you like and appreciate their answers please CLICK the like icon (thumbs up) ]

Yes you could do it with "If" like this:

Sum( If( Target=0) , Achieved )

or with "Set Expression" like this

Sum( {<Target={0}>} Achieved )

 

 

marishnagendran
Contributor

Re: Straight table

In  Transaction table column Target and achieved or calculated field

In my output if i add customer name your expression is working. but i don't to show customer name

MVP
MVP

Re: Straight table

It get it to work perfectly for me without involving the customer name or showing the customer name.... that is if you orginal "transaction table" is like you said it is...

marishnagendran
Contributor

Re: Straight table

Sorry that is not my transaction table.

In my front end i calculated the target and achieved column.

BranchCustomerTargetAchieved
    
Branch 1Customer 110050
Branch 1Customer 210020
Branch 1Customer 3020
Branch 1Customer 4040
Branch 2Customer 14020
Branch 2Customer 5050

Above i calculated the target and achieved column based on expression

I need output like this

 

Branch TargetAchievedWithout Target Achieved
    
Branch 120013060
Branch 2407050

 

MVP
MVP

Re: Straight table

Could you please provide more specific details so we don't have to guess how you underlying calculations or base tables are? Maybe you have a small sample application that you could share with the relevant data, charts and expressions?

 

 

marishnagendran
Contributor

Re: Straight table

This is a requirement

BranchCustomerTargetAchieved
    
Branch 1Customer 110050
Branch 1Customer 210020
Branch 1Customer 3020
Branch 1Customer 4040
Branch 2Customer 14020
Branch 2Customer 5050

 

The above is my straight table in Qliksense. In that Target and Achieved are measures Branch and customer are dimensions

For Management peoples they need

Branch TargetAchievedWithout Target Achieved
    
Branch 120013060
Branch 2407050

 

Customer whose have a O value in Target.But sales is happens then i want to show that value in Without Target Achieved Column

Out Put table

Branch is dimension

Target,Achieved,Without Target Achieved are measures.

 

MVP
MVP

Re: Straight table

So how does the expressions for the Target and Achieved measures look like? The full expresson with all operators and functions?

marishnagendran
Contributor

Re: Straight table

Max fiscal year and max of month is used in that expression

MVP
MVP

Re: Straight table

Max fiscal year and max month can't really return an amount... so it doesn't seem to be the full expression... 

Since you're not giving a complete expression I can only hint in which direction you have to try.... something like this:

Sum( Aggr( If( <target-expression> =0,  <amount-expression> ) , dim1 , dim2 , ... )