Hi,
I'm Showing 1 KPI for Q1'20 and one for YoY - Q1'19
i was asked to add 3 more KPI's under Q1'19
Jan-19 Feb-19 March-19
I have written this set analysis, but it doesn't seem to work when i select a different timeline.
can anyone please help come up with a smarter way? that would also work for different date selected?
this what i put under my first Q1 KPIs
if('Q' & ceil(num(Month(today()))/3)='Q1',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'1'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'1'} >} $(vList_Price)))
,
if('Q' & ceil(num(Month(today()))/3)='Q2',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'4'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'4'} >} $(vList_Price))),
if('Q' & ceil(num(Month(today()))/3)='Q3',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'7'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'7'} >} $(vList_Price))),
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'10'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'10'} >} $(vList_Price))))))
for Q2 KPIs
if('Q' & ceil(num(Month(today()))/3)='Q1',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'2'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'2'} >} $(vList_Price)))
,
if('Q' & ceil(num(Month(today()))/3)='Q2',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'5'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'5'} >} $(vList_Price))),
if('Q' & ceil(num(Month(today()))/3)='Q3',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'8'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'8'} >} $(vList_Price))),
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'11'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'11'} >} $(vList_Price))))))
and for Q3
if('Q' & ceil(num(Month(today()))/3)='Q1',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'3'}>} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'3'} >} $(vList_Price)))
,
if('Q' & ceil(num(Month(today()))/3)='Q2',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'6'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'6'} >} $(vList_Price))),
if('Q' & ceil(num(Month(today()))/3)='Q3',
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'9'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'9'} >} $(vList_Price))),
(Sum({$< [IS Discount (Y/N)] = {'Y'} ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'12'} >} $(vDiscount))
/
Sum({$< [IS Discount (Y/N)] = ,[Year Diff] = {"$(=max([Year Diff])-1)"}, [Month Num] = {'12'} >} $(vList_Price))))))
