Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

Compare two date fields using set analysis in table

Hi,

i have table with Date dimension and measures. Here i want to compare dimension date with canonical date in measures and get value for only that date.

Dimension:

campaign_start_date


Measure:

count({$<CanonicalDate = {'=campaign_start_date'},DataType = {'opens'}>}distinct invitation_id)

Here i want count of invitation where campaign start date equals canonical date

Please let me know if i you need more info.

Please help me on this

5 Replies
shraddha_g
Honored Contributor III

Re: Compare two date fields using set analysis in table

try

count(distinct {$<CanonicalDate = p(campaign_start_date),DataType = {'opens'}>} invitation_id)

berryandcherry6
Contributor II

Re: Compare two date fields using set analysis in table

i am getting wrong counts. Can i know use of P() here. Because i want to just compare date with dimension date and get opens count for that date.

shraddha_g
Honored Contributor III

Re: Compare two date fields using set analysis in table

Refer P() &amp; E() and where do you use them?

Also if possible share sample app

MVP
MVP

Re: Compare two date fields using set analysis in table

You cannot use a set expression for row by row calculations. The set expression is executed once before the chart is built not on a row by row basis. You options are to use a replace the set with a Sum(If()) expression, or to add a derived flag fields in the load script and use the flag field in a set expression.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
berryandcherry6
Contributor II

Re: Compare two date fields using set analysis in table

got it, thanks!

Community Browser