15 Replies Latest reply: Aug 17, 2015 10:37 AM by Danila Gromov

# 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!

• ###### Re: Count if (DateA-DateB<=XX)

Try this:

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

• ###### Re: Count if (DateA-DateB<=XX)

- 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)

• ###### Re: Count if (DateA-DateB<=XX)

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

• ###### Re: Count if (DateA-DateB<=XX)

Thanks everyone

• ###### Re: Count if (DateA-DateB<=XX)

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?

• ###### Re: Count if (DateA-DateB<=XX)

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

[EventA],

[EventB],

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

FROM ...;

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

• ###### Re: Count if (DateA-DateB<=XX)

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!

• ###### Re: Count if (DateA-DateB<=XX)

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))

• ###### Re: Count if (DateA-DateB<=XX)

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

• ###### Re: Count if (DateA-DateB<=XX)

New question:)

One colomn has numbers of unique transactions formatted like:

xz123456789ys, where xz,ys - letters

I need to find out transactions which start with letters ZX and finish with letters SD

like

ZX144278123SD

ZX789654222SD etc

If colomn name is ID what formula should be?

• ###### Re: Count if (DateA-DateB<=XX)

If( Left (ID,2) ='ZX' and Right(ID,2) ='SD', 1,0) as IDFlag

or

If ( ID Like 'ZX*SD', 1,0) as IDFlag

or as calculated dimension:

=If( Left (ID,2) ='ZX' and Right(ID,2) ='SD', ID)

etc.

or using set analysis:

Count({<ID = {"ZX*SD"}>} Distinct ID)

• ###### Re: Count if (DateA-DateB<=XX)

Thanks!

And what if i need to separate all IDs into 3 groups:

1. 'ZX*SD'+'ZX*FG'

2. 'LK*SD'

3. others

If ( ID Like 'ZX*SD' and Like 'ZX*FG', 1,0) as ZX

If ( ID Like 'LK*SD', 1,0) as LK

and how to group rest of IDs to make 3rd group?

Something like:

If ( ID NotLike 'ZX*FG' and NotLike 'ZX*SD' and NotLike 'LK*SD', 1,0) as Rest

Another step is to add a filter to worksheet to switch between this groups

Now I can add separate filters for ZX and LK and choose between 1 and 0 to count or not count such strings

I'd like to have 1 filter that allows turning on/off all of 3 groups ( ZX,LK,Rest)

I guess I need smth like setting (ZX,LK,Rest) as TypeOfOperation and adding filter TypeOfOperation

Another question

I need to count number of strings which have equal text in 2 different colomns

This seems very easy, I try:

Count({<Colomn1={"=Colomn2"}>} Colomn1)

Count({<Colomn1={"=[Colomn2]"}>} Colomn1)

Count({<Colomn1={"=[Colomn2]"}>} [Colomn1])

and none of it work

• ###### Re: Count if (DateA-DateB<=XX)

So, I managed to fix database to have 3 groups for 1st question

Still cannot find strings with equal colomns

I have one file showing Region and fact Sorting Center (SC) for real transactions

1.NY - SC1

2.NY - SC3

3.LA - SC2

4.MA - SC1

5.WY - SC1

6.MA - SC2

Another file has Regions and Target Sorting Centers:

NY - SC1

LA - SC1

MA - SC1

WY - SC3

I need to separate strings which were presorted to right (Target)  sorting center

In my example strings 1,4 were presorted right

I tried to put it in script, like that:

FactSC&'='&TargetSC as RightPresorting

FactSC&'<>&TargetSC as WrongPresorting

But FactSC and TargetSC are loaded from different sources and QlikSence shows mistake

Another way is to make formula for pivot table, I tried

Count({\$<TARGETSC={"=[FactSC]}>} [TARGETSC]) which doesnt work

Any thoughts on right formula/ approach?

• ###### Re: Count if (DateA-DateB<=XX)

You can MAP your targets to your facts and then perform the check:

MAP:

Region, [Target Sorting Center]

FROM 'AnotherFile';

if([Sorting Center] <> Target, 'Wrong','Right') as Presorted;

Region,

[Sorting Center],

Applymap('MAP',Region,'no mapping') as Target

FROM 'OneFile';

• ###### Re: Count if (DateA-DateB<=XX)

I tried to modify script several times and still no success

In fact my task is a bit more complicated than I stated in 1st message

1st file has 1 mln strings with factSC full name and destination index (the task is about logistics)

2nd file has correspondence between factSC full name and factSC

3rd file has correspondence between destination index and destination region

4th file has correspondence between region and targetSC

I need to find out strings which were prosorted right, factSC=targetSC

What I try is:

MAP:

factSC full name, index

FROM 1st file;

factSC full name, factSC

FROM 2nd file;

index, region

FROM 3rd file;

Region, targetSC

FROM 4th file;