Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Shir63
Contributor III
Contributor III

showing 3 months KPI for each Qtr based on selections

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))))))

 

 

0 Replies