Able to create a measure using outputs of other measures?

[#1]

Hi all,

I'm very new to Qlik, so apologies if I'm way away on this but here goes...

I have a database that manages leads. So far I have created the following measures:

• Count of all leads....  count(LeadID)
• Leads converted... count({$<[DateCompleted]-={"NULL"}>}  [DateCompleted])
• Leads cancelled... count({$<[DateCancelled]-={"NULL"}>}[DateCancelled])

I now need to calculate leads being investigated... which in theory is this calculation of the measures above:

'Count of all leads' MINUS 'Leads converted' MINUS 'Leads cancelled'.

Is it possible to do this calculation as a measure within in Qlik? Something like...

SUM(Count(LeadID)) MINUS (count({$<[DateCompleted]-={"NULL"}>}  [DateCompleted]) MINUS (count({$<[DateCancelled]-={"NULL"}>}  [DateCancelled]))

Or do I need to be approaching this in a different way?

Many thanks in advance,

Dan

[krishnacbe]

Hi,

If you label each expression, then you can use the label to create new expression.

Exp1=count(LeadID)

Exp2=count({$<[DateCompleted]-={"NULL"}>}  [DateCompleted])

Exp3=count({$<[DateCancelled]-={"NULL"}>}[DateCancelled])

Exp4 = Exp1 - Exp2 - Exp3

[Anil_Babu_Samineni · Accepted solution]

May be this?

(Count(LeadID)) - (count({$<[DateCompleted]-={"NULL"}>}  [DateCompleted]) - count({$<[DateCancelled]-={"NULL"}>}  [DateCancelled]))

[#4]

Thank you Anil Babu Samineni, that worked! I just need to change the minus to a plus to add the the Date Completed and Date cancelled fields.

This has been a great help, thank you very much!

[#5]

Thank you Krishnapriya Arumugam for your reply. I wasn't able to set each expression within the measure, I could only set one?

I had the error 'garbage after 'Exp2''. Any idea what could cause this?

Many thanks

[krishnacbe]

Hi,

Hope you have created this expression in Script. Please end the expressions with Semicolon.

SET Exp1=count(LeadID);

SET Exp2=count({$<[DateCompleted]-={"NULL"}>}  [DateCompleted]);

SET Exp3=count({$<[DateCancelled]-={"NULL"}>}[DateCancelled]);

SET Exp4 = Exp1 - Exp2 - Exp3;