Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator II
Creator II

Populate or Hide Quarters based on Data

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?

Capture.JPG

Output:

CompQ1
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 🙂

1 Solution

Accepted Solutions
jonathandienst

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.

Capture.PNG

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
asinha1991
Creator III
Creator III

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
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
qlikwiz123
Creator II
Creator II

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

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.

Capture.PNG

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

qlikwiz123
Creator II
Creator II

Nice, thank you so much.