Count if (DateA-DateB<=XX)

[#1]

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!

[#2]

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?

[swuehl]

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)

[#4]

Thanks!

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

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

2. 'LK*SD'

3. others

I add to script:

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

[#5]

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?

[swuehl]

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

MAP:

MAPPING LOAD

Region, [Target Sorting Center]

FROM 'AnotherFile';

LOAD *,

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

LOAD

     Region,

     [Sorting Center],

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

FROM 'OneFile';

[#7]

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:

MAPPING LOAD

factSC full name, index

FROM 1st file;

MAPPING LOAD

factSC full name, factSC

FROM 2nd file;

MAPPING LOAD

index, region

FROM 3rd file;

MAPPING LOAD

Region, targetSC

FROM 4th file;

LOAD *,

      if([factSC] <> targetSC 'Wrong','Right') as Presorted;

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

I wonder if I can make 4 MAPPING LOADs

What is Applymap for?

What source I put after it ( it has data from different files (2nd and 4th)?