Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count if (DateA-DateB<=XX)

Hello there!

I have a problem with formulas in Qlik Sense

Need to count all cases in which difference between two dates is less or equal to set period

Trying this expression:

Count({<([DateA]-[DateB])={"<=13"}>} [DateA])

and have "Error in set modifier expression"

What's wrong with it?

Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

- Within the set expression set modifier, only field names are allowed left of an equal sign (the equal sign is not to be read as comparison, but as assignment operator).

- What Sunny suggests is syntactically correct, but won't really work if a DateA value could have more than one relation to a DateB value (so the advanced search can result in an ambiguous answer). You would need to operate the set modifier on a key field for DateA - DateB relations to get a correct result.

=Count({<DateCombinationKey = {"=DateA - DateB <= 13"}>} DateCombinationKey)

View solution in original post

15 Replies
sunny_talwar

Try this:

Count({<DateA = {"=DateA - DateB <= 13"}>} [DateA])

swuehl
MVP
MVP

- Within the set expression set modifier, only field names are allowed left of an equal sign (the equal sign is not to be read as comparison, but as assignment operator).

- What Sunny suggests is syntactically correct, but won't really work if a DateA value could have more than one relation to a DateB value (so the advanced search can result in an ambiguous answer). You would need to operate the set modifier on a key field for DateA - DateB relations to get a correct result.

=Count({<DateCombinationKey = {"=DateA - DateB <= 13"}>} DateCombinationKey)

Not applicable
Author

Thanks everyone

sunny_talwar

That was very informative Stefan. I was never really sure how this worked, but I think from you response, I created a test sample and decided to understand the logic of what you explained above. And after testing it out, it makes perfect sense. I might still need to spend some more time on this to fully digest it, but I have a good base now.

Best,

Sunny

Not applicable
Author

Guys, I have questions again;)

I need to find difference between average of columns (dates for Event A and Event B) for 1mln strings

So i'm using: Avg([Date_of_event_B])-Avg([Date_of_event_A]) which works well

In real life event B always goes after event A

Problem is that sometimes dates in inform systems are wrong, so I receive negative value for [Date_of_event_B]-[Date_of_event_A]

So i need to count Avg([Date_of_event_B)-Avg([Date_of_event_A]) only for those strings where [Date_of_event_B]-[Date_of_event_A]>0 not taking rest of strings into account

I can count number of such strings with formula:

Count({<EventAToEventB={"=[EventB]-[EventA]>0"}>} EventAToEventB) where EventAToEventB is date combination key

But Avg({<EventAToEventB={"=[EventB]-[EventA]>0"}>} EventAToEventB) doesnt work, showing "-" values

What should be right formula?

swuehl
MVP
MVP

What is the format of field EventAToEventB, a text format?

I think you would want something like

Avg({<EventAToEventB={"=[EventB]-[EventA]>0"}>} [EventB]-[EventA])

or create a field with the difference in your script, like

LOAD

     [EventA],

     [EventB],

     [EventB]-[EventA] as [B-A]

FROM ...;

=Avg({<[B-A] = {">=0"}>} [B-A])

Not applicable
Author

format of fields EventA, EventB is

01.06.2015 21:38


EventAToEventB is a field created in script:

EventB&'-'&EventA as EventAToEventB

I guess format is just number

I tried =Avg({<[B-A] = {">=0"}>} [B-A]) and it returns "-" values

but this thing worked out!

Avg({<EventAToEventB={"=[EventB]-[EventA]>0"}>} [EventB]-[EventA])


Thank you!

Not applicable
Author

Next step

If I need to limit possible values from both sides?

Count Avg([Date_of_event_B)-Avg([Date_of_event_A]) only for those strings where

[Date_of_event_B]-[Date_of_event_A]>0

and

[Date_of_event_B]-[Date_of_event_A]<200

not taking rest of strings into account


I tried

Avg({<EventAToEventB={"=[EventB]-[EventA]>0"}>} {<EventAToEventB={"=[EventB]-[EventA]<200"}>}[EventB]-[EventA])

which works wrong ( returns negative values, I guess it takes only 2nd limitation (<200))

swuehl
MVP
MVP

Avg({<EventAToEventB={"=[EventB]-[EventA]>0 and [EventB]-[EventA]<200"}>} [EventB]-[EventA])