Calculate Measure issue in Qlik Sense

[sat_tok52]

Hi All,

I have a measure column is Sum(Sales) and i have to create one more measure  conditionally display measure , it means i have another column called SalesRate this SalesRate column data  have written in INLINE table like below.

Load * Inline [

SalesRate,Target

100,1.0

200,2.0

300,3.0

];

I have written below expression ,but getting null value in my measure column,

=IF(Sum(Sales)=SalesRate,Traget)

How to display my target data in measure column. Any help

[JordyWegman]

Hi,

The reason why it doesn't work is because there is no relation between the two tables. 

What you need is the following formula:

IF( Sum(Sales) = 100, 1.0,
  IF( Sum(Sales) = 200, 2.0,
    IF( Sum(Sales) = 300, 3.0, Sum(Sales)
    )
  )
)

Jordy

Climber

[sat_tok52]

Hi Jordy,

Thanks for your reply.

If it is 3 0r 4 line's of data i can write like your expression ,but my inline have 120 lines.

Is there any alternate way.

Sateesh

[JordyWegman]

Hi Sateesh,

That would have been some good information to know.

Then this is your solution. Use an ApplyMap to prepare this and execute this in your fact table.

mapSalesRate2Target:
Mapping Load
  SalesRate,
  Target
;
Load * Inline [
SalesRate,Target
100,1.0
200,2.0
300,3.0
];

FactTable:
Load
 *
 applymap('mapSalesRate2Target',Sales,0) as Target
From [YourSource];

 Jordy

Climber