11 Replies Latest reply: May 14, 2018 8:41 AM by Sunny Talwar

# Fomula Help - Dates and Measure

Hi,

I need to create a formula for a line chart please.

I have 2 measures already in place that currently produce 2 separate lines within the chart

Actual:

Sum({<Period = {"2018*"}, [Country] = {'CAN'}, Lookup = {'Extension'}>} Val)

Forecast:

alt(sum({<Period={"2018*"},[Sheet1.Country]={'CAN'},[Sheet1.Dashboard Name]={'P and N'}>}[Sheet1.Forecast]),NULL())

I want to apply logic within the FORECAST formula to say -

IF DATE <TODAY THEN ACTUAL ELSE FORECAST

• ###### Re: Fomula Help - Dates and Measure

May be just this

If(Date < Today(),

Sum({<Period = {"2018*"}, [Country] = {'CAN'}, Lookup = {'Extension'}>} Val),

Alt(Sum({<Period = {"2018*"}, [Sheet1.Country] = {'CAN'}, [Sheet1.Dashboard Name] = {'P and N'}>}[Sheet1.Forecast]), Null()))

• ###### Re: Fomula Help - Dates and Measure

Hi Sunny,

The line now just disappears when I apply the logic you have suggested.

• ###### Re: Fomula Help - Dates and Measure

Do you have a datefield called Date?

UPDATE: And you are using this date as your dimension?

• ###### Re: Fomula Help - Dates and Measure

No, I don't have a date field called Date. I only have period as a date which is formatted YYYYMM and this is used within my dimension and I have extra logic within the dimension to format the date for the chart purpose

Period:=Month(Date#(right(Period,2),'MM'))

• ###### Re: Fomula Help - Dates and Measure

May be this then

If(Period <= MonthStart(Today()),

Sum({<Period = {"2018*"}, [Country] = {'CAN'}, Lookup = {'Extension'}>} Val),

Alt(Sum({<Period = {"2018*"}, [Sheet1.Country] = {'CAN'}, [Sheet1.Dashboard Name] = {'P and N'}>}[Sheet1.Forecast]), Null()))

• ###### Re: Fomula Help - Dates and Measure

That doesn't seem to resolve the issue either, I don't actually see my line anymore by applying this logic.

Could it be due to the fact this logic comes from one table

Sum({<Period = {"2018*"}, [Country] = {'CAN'}, Lookup = {'Extension'}>} Val),

and this logic comes from another

Alt(Sum({<Period = {"2018*"}, [Sheet1.Country] = {'CAN'}, [Sheet1.Dashboard Name] = {'P and N'}>}[Sheet1.Forecast]), Null()))

?

The tables are joined so wouldn't have thought that is the issue.

• ###### Re: Fomula Help - Dates and Measure

Would you be able to share the logic that you use to generate the Period field? Is this read as date or string or number?

Why don’t my dates work?

QlikView Date fields

Get the Dates Right

• ###### Re: Fomula Help - Dates and Measure

The data is imported from Access and comes through as YYYYMM.

On the data manager within Qlik the Period is a dimension. I have created Period_updated as a calculated field by using Date(date#(Period,'YYYYMM'),'DD/MM/YYYY') but I don't get any data returned within my chart when I change from Period to Period_updated

Not too sure what else I can tell you really.

• ###### Re: Fomula Help - Dates and Measure

Would it be possible for you to share a sample to look at?

• ###### Re: Fomula Help - Dates and Measure

Is there anything in particular you would want to see?

• ###### Re: Fomula Help - Dates and Measure

I just want to see the setup for your chart... dimensions, expressions, sorting etc. I also want to see the formats of your date fields. May be look at the data model.

• ###### Re: Fomula Help - Dates and Measure

Convert Period to a Date to compare to Today()

MakeDate(Date#(left(Period,4),Date#(right(Period,2))