Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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.
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;
One way may be like this:
Set vMY = '=Max(Year)';
Set vMQ = '=MaxString({<Year = {$' & '(vMY)}>} Quarter)';
Sum({<Year = {$(vMY)}, Quarter = {"<=$(vMQ)"}>} Amount) /
Sum({<Year = {$(=vMY - 1)}, Quarter = {"<=$(vMQ)"}>} Amount)
(Sum({<Year = {$(vMY)}, Quarter = {"<=$(vMQ)"}>} Amount) -
Sum({<Year = {$(=vMY - 1)}, Quarter = {"<=$(vMQ)"}>} Amount)) /
Sum({<Year = {$(=vMY - 1)}, Quarter = {"<=$(vMQ)"}>} Amount)
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.
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.
Nice, thank you so much.