topic Re: Date in QlikView

Date

mario-sarkis — Wed, 01 Jul 2015 07:52:04 GMT

Hey all,

my question is i have a field of Date and amount for example:

PerioDate : is to field of Date as number

i need to calculate in a BAR CHART

1- Sum({<periodDate=MaxDate>}amount) (Current date)

2- Sum(({<periodDate=YearAgo>}amount) (YearAgo)

3- Sum(({<periodDate=LastMonth>}amount) (Last Month)

Please help

Re: Date

Wed, 01 Jul 2015 07:58:08 GMT

Hi,

Try like below,

Exp: =Sum({<periodDate={"$(=Max(periodDate))"}>}amount) (Current date)

Calculate field at script level like below,

year(periodDate) as periodDateYear

Then, Take below Dimension & expression under bar chart

Dim:periodDateYear

Exp: =Sum({<periodDateYear={"$(=year(periodDate)-1)"}>}amount) (YearAgo)

Calculate field at script level like below,

month(periodDate) as periodDateMonth

Then, Take below Dimension & expression under bar chart

Dim:periodDateMonth

Exp: =Sum({<periodDateMonth={"$(=month(max(periodDate))-1)"}>}amount) (Last Month)

Re: Date

SatyaPaleti — Wed, 01 Jul 2015 08:06:50 GMT

Mario,

Try this

Sum({<periodDate={"$(=Max(periodDate))"}>}amount) as Current date

Sum({<periodDateYear={"$(=Year(periodDate)-1)"}>}amount) as YearAgo

Sum({<periodDateMonth={"$(=Month(max(periodDate))-1)"}>}amount) as Last Month

Thank you,

Satya paleti

Re: Date

mario-sarkis — Wed, 01 Jul 2015 08:42:29 GMT

Hi Satya

if i am in 2/27/2015 and want the summ in 2/27/2014 is the expression of year ago that you gave me will be applicable ?

Thank you,

Re: Date

SatyaPaleti — Wed, 01 Jul 2015 08:45:46 GMT

Mario,

Try This

Sum({<periodDateYear={"$(=Max(Year(periodDate))-1)"}>}amount) as YearAgo

Thank you,

SatyaPaleti

Re: Date

PrashantSangle — Wed, 01 Jul 2015 08:51:37 GMT

Hi,

try below

Sum({<periodDate={"$(=Max(periodDate))"}>}amount) as Current date

Sum({<periodDate={"$(=AddYears(max(periodDate),-1))"}>}amount) as YearAgo

Sum({<periodDate={"$(=AddMonths(max(periodDate),-1)"}>}amount) as Last Month

Note: check Dateformat

Regards

Re: Date

mario-sarkis — Wed, 01 Jul 2015 09:06:39 GMT

hi Max PeriodDate format is num is this still applicable thanks:)

Re: Date

PrashantSangle — Wed, 01 Jul 2015 09:12:05 GMT

Hi,

I think above expression will work for you, If not use Num()

Modify above expression like

Sum({<periodDate={"$(=Max(periodDate))"}>}amount) as Current date

Sum({<periodDate={"$(=num(AddYears(max(periodDate),-1)))"}>}amount) as YearAgo

Sum({<periodDate={"$(=num(AddMonths(max(periodDate),-1)))"}>}amount) as Last Month

Regards

Re: Date

mario-sarkis — Wed, 01 Jul 2015 10:45:22 GMT

hey Max thank you for your help ,

but i am facing a small problem wich is :

lets say i have the date 2/27/2015 but in on year back i have it as 2/26/2014 i dont have 2/27/2014

so using your expression it will show a zero amount in 2/27/2014 (amount is avaible on day in a month elsa null)

is it possible to create a field that contain YEARMONTH ? if yes how and how can i use it in this expression

Thank you hope that you can help.

Re: Date

PrashantSangle — Wed, 01 Jul 2015 10:56:05 GMT

Hi,

If you want to create YearMonth field then you can use MonthName() it will gives MonthYear

You can also use Date(Fieldname,'YYYYMM') as YearMonth

Regards

Re: Date

mario-sarkis — Wed, 01 Jul 2015 11:19:05 GMT

hey Max sorry for disturbing i used it in the expresseion as below

Sum({<periodDate={"$(=num(MonthName(AddYears(max(periodDate),-1))))"}>}amount)

it give me zero so i thinking of greating the field YearMonth in the script. and see.

Thank you alot hope it will work:)

Re: Date

PrashantSangle — Wed, 01 Jul 2015 11:23:16 GMT

Hi,

Is it possible to post your app???

Regards