topic Re: Populate or Hide Quarters based on Data in QlikView

Populate or Hide Quarters based on Data

qlikwiz123 — Sat, 16 Nov 2024 20:57:22 GMT

I have years (Current Year and Previous Year) and quarters. I need to show the change of difference over the year in Quarters. Since Current year is 2019 and we still are in May, the Quarters don't go beyond Q1. In this case, the table shows -100% which is mathematically correct but kind of deceptive. How do I dynamically create this table so that data is shown only when data is present in respective Quarters?

Output:

Comp	Q1
CompA	-36%
CompB	-25%

As we go to the next Quarter, the data becomes available and the table should add Quarters dynamically. By end of this year, the output table should have all the 4 quarters.

@sunny_talwar Any help is really appreciated 🙂

Re: Populate or Hide Quarters based on Data

asinha1991 — Thu, 02 May 2019 05:11:53 GMT

I need to see what you are currently doing to advise exact solution but you can add conditional loads in script..

for example:

Data:

Load 'Q1' as Quarter, somevalue...resident sometable;

if num(month(now())) >=3 then

concatenate(Data)

Load 'Q2' as Quarter, somevalue...resident sometable;

end if;

if num(month(now())) >=6 then

concatenate(Data)

Load 'Q3' as Quarter, somevalue...resident sometable;

end if;

if num(month(now())) >=9 then

concatenate(Data)

Load 'Q4' as Quarter, somevalue...resident sometable;

end if;

Re: Populate or Hide Quarters based on Data

jonathandienst — Thu, 02 May 2019 06:08:01 GMT

One way may be like this:

Define the variables in the load (vMQ is split to defer the $ expansion and gets the max Q from the current year):

Set vMY = '=Max(Year)';
Set vMQ = '=MaxString({<Year = {$' & '(vMY)}>} Quarter)';

Then use the ratio:

Sum({<Year = {$(vMY)}, Quarter = {"<=$(vMQ)"}>} Amount) /
Sum({<Year = {$(=vMY - 1)}, Quarter = {"<=$(vMQ)"}>} Amount)

Or the variance:

(Sum({<Year = {$(vMY)}, Quarter = {"<=$(vMQ)"}>} Amount) -
Sum({<Year = {$(=vMY - 1)}, Quarter = {"<=$(vMQ)"}>} Amount)) /
Sum({<Year = {$(=vMY - 1)}, Quarter = {"<=$(vMQ)"}>} Amount)

Finally, make sure that suppress zeroes is enabled for the table

Dynamically add quarters

qlikwiz123 — Mon, 06 May 2019 13:34:22 GMT

Hi @jonathandienst

The given expression isn't working. Also, the vMQ variable is giving an error. So I created vMQ1 inside the Variable Overview to check and it works.

I am attaching my qvw file. I want to show the difference between the current year and previous year in Quarter manner. If you look at the table, the difference % makes sense since we are already in Q1 2019. But for Q2, Q3, Q4, the value is -100% since we are not yet there. I want to make the calculation dynamic such that as we are in Q1, the table should show only Q1 and as we progress in the year towards the end, it should show Q2, Q3 and Q4.

Re: Populate or Hide Quarters based on Data

jonathandienst — Fri, 03 May 2019 05:50:08 GMT

My bad, vMQ should be defined with a LET statement. But having said that, I could not get the expression to work in your sample for some reason. But i did get this working:

(Sum({<Year = {$(=vMY)}>} Amount) / Sum({<Year = {$(=vMY-1)}>} Amount) - 1) * Sign(Sum({<Year = {$(=vMY)}>} Amount))

This does not make use of vMQ, but suppresses the expression when the value for the current year is zero.

Re: Populate or Hide Quarters based on Data

qlikwiz123 — Mon, 06 May 2019 13:35:09 GMT

Nice, thank you so much.