topic Measures from different fields based on Current Month in QlikView

Measures from different fields based on Current Month

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

H i All,

I am having two different tables having Site,Year Month & Actual from one table and Site ,Year Month&Forecast value from other table.

Here my scenario is based on Current month I have to show Actual & Forecast.

The bar chart based on Year Month dimension need to plot the values of Forecast from May 2017 to Dec 2017 (As we are in May 2017) and Actual should show only from Jan2017 to Apr 2017. I have attached sample file. Let me know if I am not clear.

-Jai

Re: Measures from different fields based on Current Month

maxgro — Sat, 06 May 2017 16:05:39 GMT

CHART

SCRIPT

LOAD Month,

Site,

Actual,

-1*(Month < MonthStart(Floor(Today()))) as FlagActual

FROM

[Actuals&Forecast.xlsx]

(ooxml, embedded labels, table is Actuals);

join (T)

LOAD Site,

Forecast,

Month,

-1*(Month >= MonthStart(Floor(Today()))) as FlagForecast

FROM

[Actuals&Forecast.xlsx]

(ooxml, embedded labels, table is ForeCast);

Re: Measures from different fields based on Current Month

Sun, 07 May 2017 18:46:26 GMT

Use Dimension- Month

Expression-if(Num(Date#(Month))<=42826,Sum(Actual),Sum(Forecast))