topic Re: Current FYQ in QlikView

Current FYQ

alexshaji — Sat, 16 Nov 2024 18:13:30 GMT

HI Members,

I have quite a few places in the Dashboard where I need to display the current and previous quarter results. My project follows the FYQ as:

Nov - Jan = Q1
Feb - Apr=Q2
May - Jul=Q3
Aug - Oct=Q4

The table has the following:

Cust ID	SalesFYQ	SalesDate	Business Unit	Sales Amount
121	FY16 Q2	24-03-2016	ABC	12000
123	FY17 Q1	16-12-2017	AAV	2000
142	FY20 Q1	21-11-2020	GBC	500
116	FY20 Q2	...	AAA	6897
1220	FY20 Q3	...	....	7789
136	FY20 Q4	...	...	69369
400	FY21 Q1	...	....	421
422	FY22 Q2	...	...	1589

Now Ideally every Quarter the display will need to change. FIr examples this quarter

Current Qtr Sales Previous Qtr Sales

FY20 Q4 = 69369 FY20 Q3 = 7789

How can I achieve this? Please help me

Re: Current FYQ

alexshaji — Mon, 17 Aug 2020 17:15:20 GMT

Guys

Re: Current FYQ

Kushal_Chawda — Mon, 17 Aug 2020 21:14:56 GMT

Create variables like below

vCurrentQuarterStart = date(quarterstart(max(SalesDate),0,11)) vCurrentQuarterEnd = date(quarterend(max(SalesDate),0,11)) vPreviousQuarterStart = date(quarterstart(max(SalesDate),-1,11)) vPreviousQuarterEnd = date(quarterend(max(SalesDate),-1,11))

Now you can use below expression to get the sales amount

Sum({<Quarter, Month,Week, SalesDate,SalesDate={">=$(vCurrentQuarterStart)<=$(vCurrentQuarterEnd)"}>}SalesAmount) Sum({<Quarter, Month,Week, SalesDate,SalesDate={">=$(vPreviousQuarterStart)<=$(vPreviousQuarterEnd)"}>}SalesAmount)

Re: Current FYQ

alexshaji — Mon, 24 Aug 2020 08:05:18 GMT

Thanks Kush. I am getting the Sum of current and previous qtrs exactly as I need. One more thing that I would request is the Header for this which also should be Dynamic.

So in the KPI i can use

FY20 Q4 FY20 Q3

69369 7789

Re: Current FYQ

Kushal_Chawda — Tue, 25 Aug 2020 20:51:50 GMT

may be below

// Current Quarter ='FY'& date(quarterstart(max(SalesDate),0,11),'YY')&' '& 'Q'&ceil(month(quarterstart(max(SalesDate),0,11))/3) // Previous Quarter ='FY'& date(quarterstart(max(SalesDate),-1,11),'YY')&' '& 'Q'&ceil(month(quarterstart(max(SalesDate),-1,11))/3)