Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

paaerts
New Contributor

sum if function?

Hi, 

I need an expression that can do the following: 

These dates present contracts which have a start and end date. I would like to compute a formula in Qlik Sense that can show all the contracts going on at a particular moment in time. So for example when looking at a specific date e.g. 7/03/2019, I would love to retrieve transaction number 4,5,6 and 7 because those are the contracts that did not end yet and are already 'ongoing'. 

question qlik.PNG

thanks in advance! 

1 Solution

Accepted Solutions
Partner
Partner

Re: sum if function?

Hi Paaerts,

Use the 'Variable Input' extension from the Qlik Dashboard bundel (Qlik Sense February). Create a variable like vTransactionsDate and put this in the variable input.Variable.pngVariable Input

 

Create a table with a dimension with the formulas:

Transaction: If( [Start date] >= $(vTransactionsDate) and [end date] <= $(vTransactionsDate), transaction,null())

 Start date: If( [Start date] >= $(vTransactionsDate), [Start date],null())

End date: If( [end date] <= $(vTransactionsDate), [end date],null())

Variable in Chart.pngAdd formulas in chart

Make sure you have include null values on for all formulas!

Then you can enter a date in the variable input box and the right contracts will be shown.

 

Jordy

Climber

Work smarter, not harder
1 Reply
Partner
Partner

Re: sum if function?

Hi Paaerts,

Use the 'Variable Input' extension from the Qlik Dashboard bundel (Qlik Sense February). Create a variable like vTransactionsDate and put this in the variable input.Variable.pngVariable Input

 

Create a table with a dimension with the formulas:

Transaction: If( [Start date] >= $(vTransactionsDate) and [end date] <= $(vTransactionsDate), transaction,null())

 Start date: If( [Start date] >= $(vTransactionsDate), [Start date],null())

End date: If( [end date] <= $(vTransactionsDate), [end date],null())

Variable in Chart.pngAdd formulas in chart

Make sure you have include null values on for all formulas!

Then you can enter a date in the variable input box and the right contracts will be shown.

 

Jordy

Climber

Work smarter, not harder