Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
There is a calculation we use for call numbers. We changed this calculation method after a certain date (April 1). Use previous calculation by this date, after this date I want to use new measure behind same KPI object.
At the same time, if the date filter is both the previous and the next dates, the old calculation method should be used for the days where the previous dates were selected, and the new calculation method should be used for the following dates.
Old calculation method: sum ({<vdn = {'34444', '35555', '36666', '37777'}>} acdcalls + abncalls) + sum ({<split = ('66', '75'}> ) (dsplit_acdcalls))
New calculation method: sum ({<vdn = ('34444', '35555', '36666', '37777'}>} acdcalls + abncalls)
Any help is appreciate
Okay, we need to add an * before the = on the [date] criteria to cause it to intersect with available [date] values.
sum( {<[date]*={"<01.04.2021"}, vdn={'34444','35555','36666','37777'}>} (acdcalls + abncalls)) + sum({<[date]*={"<01.04.2021"}, split = {'66', '75'}>} dsplit_acdcalls)
+
sum( {<[date]*={">=01.04.2021"}, vdn={'34444','35555','36666','37777'}>}acdcalls + abncalls)
Use both calculations in your expressions and add criteria to the set analysis of each to tell Qlik when to use each. You can add them together because for every date, one of the sum() expressions will result in 0. You'll need to replace [DateField] with the actual field name and may need to adjust date format.
sum ({<[DateField]={"<04/01/2021"},vdn = {'34444', '35555', '36666', '37777'}>} acdcalls + abncalls) + sum ({<split = ('66', '75'}> ) (dsplit_acdcalls))
+
sum ({<[DateField]={">=04/01/2021"},vdn = ('34444', '35555', '36666', '37777'}>} acdcalls + abncalls)
hi Gary,
I changed it a bit and applied the calculation as follows:
formula:
sum( {<[date]={"<01.04.2021"}, vdn={'34444','35555','36666','37777'}>} (acdcalls + abncalls)) + sum({<[date]={"<01.04.2021"}, split = {'66', '75'}>} dsplit_acdcalls)
+
sum( {<[date]={">=01.04.2021"}, vdn={'34444','35555','36666','37777'}>}acdcalls + abncalls)
I have implemented this solution now, but I see that the kpi results remain the same when I change the dates using the date filter. Where could I be making a mistake?
Example:
select date = 23.03.2021, call number = 1000
select date range = 21.03.2021 - 24.04.2021, call number = 1000
Okay, we need to add an * before the = on the [date] criteria to cause it to intersect with available [date] values.
sum( {<[date]*={"<01.04.2021"}, vdn={'34444','35555','36666','37777'}>} (acdcalls + abncalls)) + sum({<[date]*={"<01.04.2021"}, split = {'66', '75'}>} dsplit_acdcalls)
+
sum( {<[date]*={">=01.04.2021"}, vdn={'34444','35555','36666','37777'}>}acdcalls + abncalls)
many thanx Gary, solved!