Populate or Hide Quarters based on Data

[qlikwiz123]

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 🙂

[asinha1991]

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;

[jonathandienst]

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

[qlikwiz123]

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.

[jonathandienst · Accepted solution]

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.

[qlikwiz123]

Nice, thank you so much.