It seams to be no logic to the order of the columns of the query result. Every combination of parameter selection yields a different column order
This site is in read only mode. Please continue to browse, but replying, likes,
and other actions are disabled for now.
cfort
3
Here is the query. But I believe I know why it happens, it’s because I’m using parameters as column names, so when you select a different option, the column name changes and so does the alphabetical order of the columns. Question would be if there is a workaround
select
a.ejercicioperiodo,
a.division,
a.area as "{{ division }}",
a.origen as "Origen",
sum(b."real") as "{{ Periodo }}",
sum(a."plan") as "Budget {{ Periodo }}",
sum(b."real") - sum(a."plan") as "{{ Periodo }} vs Budget Var.Abs.",
(sum(b."real") - sum(a."plan")) / sum(a."plan") * 100 as "Budget Var. %",
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end as "{{ ejerciciocomparar }}",
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end as '{{ panterior }}',
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) as "{{ Periodo }} vs {{ ejerciciocomparar }} Var. Abs.",
(
(
(
sum(b."real") - (
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
)
) / sum(b."real")
) * 100
) as "Var % {{ ejerciciocomparar }}",
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) as "{{ Periodo }} vs {{ panterior }} Var. Abs.",
case
when (
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) / (
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) * 100
) < 0 then (
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) / (
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) * 100
) * -1
else (
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) / (
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) * 100
)
end as "Var % {{ panterior }}",
a.orden
from
cached_query_242 a
join cached_query_243 b on a.area = b.area
and a.division = b.division
and a.ejercicioperiodo = b.ejercicioperiodo
and a.origen = b.origen
and a.ejercicioperiodo = '{{ Periodo }}'
and a.division = '{{ division }}'
group by
a.ejercicioperiodo,
a.division,
a.area,
a.origen
union
--------------------------------------------------------------------------------------------------------------
select
a.ejercicioperiodo,
"{{ division }}" as division,
"Todas las Areas " as "{{ division }}",
"Total {{ division }}" as origen,
sum(b."real") as "{{ Periodo }}",
sum(a."plan") as "Budget {{ Periodo }}",
sum(b."real") - sum(a."plan") as "{{ Periodo }} vs Budget Var.Abs.",
(sum(b."real") - sum(a."plan")) / sum(a."plan") * 100 as "Budget Var. %",
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end as "{{ ejerciciocomparar }}",
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end as "{{ panterior }}",
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) as "{{ Periodo }} vs {{ ejerciciocomparar }} Var. Abs.",
(
(
(
sum(b."real") - (
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ ejerciciocomparar }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
)
) / sum(b."real")
) * 100
) as "Var % {{ ejerciciocomparar }}",
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) as "{{ Periodo }} vs {{ panterior }} Var. Abs.",
case
when (
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) / (
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) * 100
) < 0 then (
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) / (
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) * 100
) * -1
else (
(
sum(b."real") - case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) / (
case
when (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
) is null then 0
else (
select
sum(c."real")
from
cached_query_243 c
where
c.area = a.area
and c.division = a.division
and c.ejercicioperiodo = '{{ panterior }}'
and c.origen = a.origen
group by
c.ejercicioperiodo,
c.division,
c.area,
c.origen
)
end
) * 100
)
end as "Var % {{ panterior }}",
4 as orden
from
cached_query_242 a
join cached_query_243 b on a.area = b.area
and a.division = b.division
and a.ejercicioperiodo = b.ejercicioperiodo
and a.origen = b.origen
and a.origen = 'Resultado Operativo'
and a.ejercicioperiodo = '{{ Periodo }}'
and a.division = '{{ division }}'
group by
a.ejercicioperiodo,
a.division,
a.origen
order by
a.area,
a.orden
jesse
4
The default table in Redash does not re-order your query result columns. They will be displayed in the same order as your SELECT statement. Are you using a pivot table or some other visualization?
cfort
7
Since the parameter selection can change and column names are based on the parameter values, every time you change the parameter the column will reorder based on the selected parameter
jesse
8
If that’s the case the only fix is to modify how your query returns columns. Redash has no “state” for the previous result of a query. It depends on the column order specified in your query. You might be better served by adding a view to your database and pointing Redash at that instead.