Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
- 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)
Try this:
Count({<DateA = {"=DateA - DateB <= 13"}>} [DateA])
- 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)
Thanks everyone
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
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?
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])
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!
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))
Avg({<EventAToEventB={"=[EventB]-[EventA]>0 and [EventB]-[EventA]<200"}>} [EventB]-[EventA])