How to create a bar chart based on date ranges

[Daniel_K]

Hello Qlik community,

I need to create a bar chart based on the data set attached which does/shows the following.

- counts based on a distinct account number

- groups by specific date ranges, presumably using the Today() function, to show the number of accounts invoiced in the last 3 months, last invoiced between 3-12 months, last invoiced over 12 months ago and no orders ever placed.

I've tried a few different ways but the result is never inline wit h expectations.

Thanks for your help!

[Vegar · Accepted solution]

Try something like these:

Invoiced in the last 3 months:
=Count({<InvoiceDate = {">=$(=num(addmonths(today(),-3)))<=$(=num(today()))"} >}distinct AccountNumber)

Last invoiced between 3-12 months:
=Count({<InvoiceDate = {">=$(=addmonths(today(),-12))<=$(=addmonths(today(),-3))"} >}distinct AccountNumber)

Last invoiced over 12 months ago:
=Count({<InvoiceDate = {"<=$(=num(addmonths(today(),-12)))"} >}distinct AccountNumber)

No orders ever placed:
=Count({<Account*=E({<InvoiceDate = {"*"}>}) >}distinct AccountNumber)

[Daniel_K]

Hello Vegar,

Thanks for the below solution, what should i put as the dimension? or should these be under dimensions and distinct count of account numbers be under expressions?

Thanks for your help so far.

Dan

[Vegar]

No or any dimension. If your chatt type does not accept no dimensjon then you can create one like this or similar. 

='Total'

[Daniel_K]

Hello Vegar,

Thank you for your reply, the dimensions side of things seems ok now.

The output I'm getting for all four expressions is the same,153, which is the count of distinct account numbers who've had an invoice raised at any time rather than within the last 3 months etc..

I suspect there is a problem with the brackets/symbols in the expression as parts of the expression like addmonths & Today() are red rather than blue

[Vegar]

I haven't got a computer available to check, but I noticed I misplaced some parentheses. 

I've edited my previous post. 

[Vegar]

Try the attached application.