Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

madeleine_josef
Contributor

Question regarding a date filter

Hello,

I have the following section in my script:

LOAD

    TRNumber,

    CompCodeTRNumberKey,

    RegistrationDate,

    ReceiptComment,

    TransactionDate as InvoiceDate,

     year (TransactionDate) as InvoiceYear,

month (TransactionDate) as InvoiceMonth,

    TransactionType,

    Account

FROM [lib://Receipt.qvd]

(qvd)WHERE match(Account, 3040, 3042, 4643, 4644, 4645, 4646, 4647, 4648, 4649)and [TransactionDate]>MakeDate(2010);

Is there any way that I can make my date filter of Invoice Year and Invoice Month to be based on only account 3040 and 3042?

Thanks!

6 Replies
MVP & Luminary
MVP & Luminary

Re: Question regarding a date filter

Like this?


WHERE

     Match(Account, 3040, 3042, 4643, 4644, 4645, 4646, 4647, 4648, 4649)

     OR ([TransactionDate]>MakeDate(2010) AND Match(Account, 3040, 3042))


talk is cheap, supply exceeds demand
madeleine_josef
Contributor

Re: Question regarding a date filter

No, I'm wondering if there's anyway to keep the script as it is and instead make a selection of accounts in the actual app where I create the datefilter?

MVP & Luminary
MVP & Luminary

Re: Question regarding a date filter

make a selection of accounts in the actual app where I create the datefilter?

Sorry, but I have no idea what you want. At least, I assume you want to do something different then make a selection of accounts which can be done in a listbox with accounts as field.


talk is cheap, supply exceeds demand
lakshmikandh
Valued Contributor II

Re: Question regarding a date filter

Try Something like this

LOAD

    TRNumber,

    CompCodeTRNumberKey,

    RegistrationDate,

    ReceiptComment,

    TransactionDate as InvoiceDate,

     year (TransactionDate) as InvoiceYear,

month (TransactionDate) as InvoiceMonth,

    TransactionType,

    Account

FROM [lib://Receipt.qvd]

(qvd)WHERE match(Account, 3040, 3042, 4643, 4644, 4645, 4646, 4647, 4648, 4649)

and  year (TransactionDate)+'-'+month (TransactionDate)

in (select  distinct

year (TransactionDate)+'-'+month (TransactionDate)

from

[lib://Receipt.qvd]

(qvd)WHERE match(Account, 3040, 3042)

)

P.S. I dont know the exact syntax for QVD load, just giving a idea ( this kind of query will work with SQL server not sure about QVD load) 

Thanks,

Lakshmikandh

brunobertels
Valued Contributor II

Re: Question regarding a date filter

Hi

may be like this :

If(Account='3040' and Account='3042,year (TransactionDate)) as InvoiceYear,

If(Account='3040' and Account='3042,month (TransactionDate)) as InvoiceMonth,

Highlighted
madeleine_josef
Contributor

Re: Question regarding a date filter

Thank you guys, I used Bruno Bertels ideá and put it in the function field for the filter.