Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Gurús,
I've developed a Sales Application in QlikSense where I have an "Invoiced Date". I need to calculate two different KPI's:
- Counter of Invoices of the last day loaded
- Counter of Invoices of the previous day of the last day loaded.
The problem is that during the weekend (or holidays) there are not invoices, so, on Monday I want to see data of Friday and Thursday (not from Sunday and Saturday). On Tuesday I want to see data of Monday and Friday.
I've tried the following formula:
Counter of Invoices of the last day loaded:
Count( distinct{<Date={'$(=max(date([Invoice Date])))'}, [Year]=,[Month]=,[Week]=>} [Invoice Number])
- Counter of Invoices of the previous day of the last day loaded:
Count( distinct{<Date={'$(=max(date([Invoice Date]-1)))'}, [Year]=,[Month]=,[Week]=>} [Invoice Number])
The first one works fine but the second one not as it's returning me one natural day before:
If I check it on Monday it returns the data for Friday and Thusday (in this case it's OK!)
But if I check it on Tuesday in returns de data for Monday and Sunday (I want to see data of Monday and Friday that are actually the last two days with invoices).
Any idea? Thanks in advance!!!
I've already found the solution for the second KPI. I attach the solution because I think it could be useful for someone else:
Count( distinct{< Date={'$(=max(date([Invoice Date]),2))'}>} [Invoice Number])
I've already found the solution for the second KPI. I attach the solution because I think it could be useful for someone else:
Count( distinct{< Date={'$(=max(date([Invoice Date]),2))'}>} [Invoice Number])