Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikwiz123
Contributor

Dynamic Quarters

I have the following bar chart built in my dashboard. I want to show only Q1 bars as there is no data for Q2, Q3 and Q4 from 2019. As the data gets available for the quarters, the bars should automatically populate for Q1 - 2018 and 2019.

1 Solution

Accepted Solutions

Re: Dynamic Quarters

Try this

Sum({<QuarterNum = {"<=$(=Max({<Year = {$(=Max(Year))}>}QuarterNum))"}>}[Funds])/1000000000

Where QuarterNum is a new field created in the script

 

Data:
LOAD Category, 
     Date, 
     'Q' & Ceil(Month([Date])/3) as Quarter,
     Ceil(Month([Date])/3) as QuarterNum,
     Year([Date]) as Year,
     Funds
FROM [Dynamic Quarters.xlsx]
(ooxml, embedded labels, table is Sheet1);
3 Replies

Re: Dynamic Quarters

Try this

Sum({<QuarterNum = {"<=$(=Max({<Year = {$(=Max(Year))}>}QuarterNum))"}>}[Funds])/1000000000

Where QuarterNum is a new field created in the script

 

Data:
LOAD Category, 
     Date, 
     'Q' & Ceil(Month([Date])/3) as Quarter,
     Ceil(Month([Date])/3) as QuarterNum,
     Year([Date]) as Year,
     Funds
FROM [Dynamic Quarters.xlsx]
(ooxml, embedded labels, table is Sheet1);
qlikwiz123
Contributor

Re: Dynamic Quarters

Perfect. Thank you so much Sunny
qlikwiz123
Contributor

Re: Dynamic Quarters

Hi @sunny_talwar 

 

Is it possible to show bars for 2018 and only hide bars for 2019 that has no data? Please refer to the attached QVW and look at Distribution - 2 chart.

 

 

I would like to see the bars as

1st column is Q2 2018 // no blank // 2nd column is Q3 2018 // no blank // 3rd column is Q4 2018 // blank //

CURRENT COMPARISON

4th column is Q1 2018 // no blank // 5th column Q1 2019 // blank // 6th column is YTD 2018 // no blank // 7th column YTD 2019