topic Re: dt in QlikView

dt

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

I have a date filed as Date which has data from 2001 to till date ..

this field iam using it in the Dimesnion .

I want to show only 2 years data like ( from jan-2015 , Feb-2015.....till date )

How should i achieve this .

Re: dt

its_anandrjs — Mon, 30 Oct 2017 13:23:52 GMT

What is data format in this field and you need this in load script or UI let me know.

Re: dt

prma7799 — Mon, 30 Oct 2017 13:30:16 GMT

in calculated dimension

=if(Match(Year(Date),2015,2016),Year,Null())

and tick suppress when value null

Re: dt

smilingjohn — Mon, 30 Oct 2017 13:30:16 GMT

Hi Anand

The date format is

01-01-2017 00:12:22

Re: dt

its_anandrjs — Mon, 30 Oct 2017 13:32:32 GMT

For which two year data you need last 2 years or it is hardcoded.

Re: dt

smilingjohn — Mon, 30 Oct 2017 13:40:58 GMT

my date format is

Re: dt

prma7799 — Mon, 30 Oct 2017 13:44:44 GMT

Could you please share some sample file?

or try like this in your back end

date(floor(timestamp#(date,'DD/MM/YYYY hh:mm:ss'))) as Date,

Year(date(floor(timestamp#(date,'DD/MM/YYYY hh:mm:ss')))) as Year

Re: dt

smilingjohn — Mon, 30 Oct 2017 13:45:01 GMT

I want to show this like this

Re: dt

Mark_Little — Mon, 30 Oct 2017 13:46:41 GMT

Hi john,

You want to start by Creating a master calendar. this will allow you then create a month year field to display as you have requested. look at the link below.

Creating A Master Calendar

Your Dimension would be MonthYear and then in your set analysis would contain {<Year={'>2014'}>}.

Mark

Re: dt

its_anandrjs — Mon, 30 Oct 2017 13:51:03 GMT

For this use this Dimension and Metrics expression.

Dim :- =MonthName(Date)

Expre:- =sum({<Date={">=$(=Date(AddMonths(Max(Date)-24)))"} >} MetricsField)

Ex:- =Sum( {<Date={">=$(=Date(AddMonths(Max(Date)-24)))"} >} Sales )

Re: dt

smilingjohn — Mon, 30 Oct 2017 13:53:07 GMT

Hi ANand please find the attachment of the QVW file ..

In dmesions i want to show from today till 2 years back data in the bar chart ...

Re: dt

its_anandrjs — Mon, 30 Oct 2017 14:05:50 GMT

May be this then, also wait for SET analysis solution

Re: dt

smilingjohn — Mon, 30 Oct 2017 14:20:49 GMT

HI Anand

If i go in 2018 then again manaully i have change this ..

Is there no default solution for this ..

Where from current year it should shwo me last two year range

Re: dt

its_anandrjs — Mon, 30 Oct 2017 14:28:42 GMT

For dynamically though you have to create variables

vCurrYr =Year(Max(Created))

vPrevYr=Year(AddMonths( Max(Created),-12))

vPrevPrevYr=Year(AddMonths( Max(Created),-24))

And then

Dim :- =If(match(Right(monthname(Created),4),$(vPrevYr),$(vPrePrevYr)),MonthName(Created))

Expre:- Sum(1)

Re: dt

its_anandrjs — Mon, 30 Oct 2017 14:33:28 GMT

Or another way is you can use the Year field but you have to add this field on your data model

Load

Created,

Year(Created) as CreatedYear

From ....

Dim:- Year

Expre =Sum({<CreatedYear={">=$(=Max(CreatedYear)-2)"}>}1)

Re: dt

smilingjohn — Tue, 31 Oct 2017 02:51:41 GMT

Hi anand

I have created the variables,

And when i add this expression ( logic ) in the dimesnion its showing error in the dimension

=If(match(Right(monthname(Created),4),$(vPrevYr),$(vPrePrevYr)),MonthName(Created))

Iam doing something wrong ?