Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
SoillBIQlikSense
Contributor
Contributor
‎2020-09-16 09:30 AM

repetition on yearmonth values in the filter

Hi I am getting repetition on yearmonth values in the filter. the data comes from 2 joined tables.I expect to get one Yearmonth value but I get multiple values when there is more than one invoice in the same month. See below.

SoillBIQlikSense_1-1600262953273.png

 

428 Views
0 Likes
1 Reply
hendrik_faurie
Partner - Contributor II
Partner - Contributor II
‎2020-09-17 10:56 AM

Hi there,

Not sure I follow... Do you mean the repetition of the InvoiceYearMonth in the tables? Or if you were to add a plain filter box object of the InvoiceYearMonth  field, do you see values duplicated in that?

In case of the latter then it means that your dates are in fact different values. Qlik Date fields can hold down to timestamp detail. Even if you see in a filter 2x the same value "2020-12-31", if you were to check with the timestamp function you'd probably see that the one is "2020-12-31 00:00:00", and the other "2020-12-31 23:59:99".

In script you can say:

Date(FLOOR(InvoiceYearMonth),'YYYYMM') AS InvoiceYearMonth

This should force all timestamps to 00:00:00 and remove "duplicated" values.

 

Shout if this didn't solve your issue and maybe then also just provide a bit more detail and will see if we can help.

397 Views