topic Re: Date format in QlikView

Date format

krishna20 — Mon, 21 Jul 2014 07:51:50 GMT

Hi Friends,

I'm having problem in date format.

=sum({$<TP_MONTH={$(=MAX(TP_MONTH)))-12)},TP_YEAR=,TP_MONTH=>}TP_GROSS)

I'm having in datamodelling TP_MONTH as JAN,FEB,MARCH,APR.....

How can i take in set analysis as 1,2,3..........

Can any one pls provide sample one..

Regards

Krishna

Re: Date format

alexandros17 — Mon, 21 Jul 2014 07:53:09 GMT

I think it is better if you ormat the month in the script

Re: Date format

MK_QSL — Mon, 21 Jul 2014 07:53:59 GMT

=SUM({$<TP_MONTH = {'$(=Date(Max(TP_MONTH)-12,'MMM'))'},TP_YEAR=,TP_MONTH=>}TP_GROSS)

Re: Date format

Mon, 21 Jul 2014 08:07:07 GMT

You can add one more month column in number format (1,2,3...) . Use that field here ..

Thanks,

TDP

Re: Date format

Mon, 21 Jul 2014 08:15:07 GMT

Change the "monthnames" in the first set expressions in the script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Re: Date format

krishna20 — Mon, 21 Jul 2014 08:29:44 GMT

Hi David,

I cant the change in the data modelling script.I'm not accessible to it. I need to change in my expression itself.So please guide me how to get in the expression.

Regards

Krirshna

Re: Date format

alexandros17 — Mon, 21 Jul 2014 08:32:38 GMT

The problem is that if you write Max(TP_MONTH) it will be the max of a string ...

Re: Date format

Anonymous — Mon, 21 Jul 2014 08:35:20 GMT

Hi,

Just to understand, you have TP_MONTH as Jan, Feb, March and you want to convert them into 1,2,3. In your script, you are subtracting 12 months from TP_MONTH which will always give you either 0 or negative month.

Try to subtract months from relevant date in set analysis script. for example below,

=Sum ({$<TP_DATE={"$(=AddMonths(Max(TP_DATE),-12))"}>} TP_GROSS)

Re: Date format

krishna20 — Mon, 21 Jul 2014 08:36:50 GMT

Hi,

Actually my requirement is to calculate (LYTD) sum( same the month of last year AMOUNT).

I'm having TP_Month format like JAN,FEB,...

I'm not getting correct result when i used the above expression

Please guide me how to calculate with the given requrement.

Regards

Krishna

Re: Date format

krishna20 — Mon, 21 Jul 2014 08:39:30 GMT

Hi,

Actually my requirement is to calculate (LYTD) sum( same the month of last year AMOUNT).

I'm having TP_Month format like JAN,FEB,...

I'm not getting correct result when i used the above expression

Please guide me how to calculate with the given requrement.

Regards

Krishna

Re: Date format

krishna20 — Mon, 21 Jul 2014 08:50:52 GMT

Hi,

Actually my requirement is to calculate (LYTD) sum( same the month of last year AMOUNT).

I'm having TP_Month format like JAN,FEB,...

I'm not getting correct result when i used the above expression

Please guide me how to calculate with the given requrement.

Regards

Krishna

Re: Date format

Anonymous — Mon, 21 Jul 2014 08:54:33 GMT

Hi,

First of all, you have to convert TP_MONTH in date format in backend,

Date(Date#(TP_YEAR&TP_MONTH, 'YYYYMMM')) as TP_DATE,

Month(Date#(TP_YEAR&TP_MONTH, 'YYYYMMM')) as TP_MONTH

And then for last year YTD you may try below,

=Sum ({$<TP_YEAR={"$(=Max(TP_YEAR)-1)"}, TP_MONTH={"<=$(=Max(TP_MONTH))"}>} TP_GROSS)

PS: There might be a typo error in the script.

Re: Date format

Mon, 21 Jul 2014 09:12:23 GMT

Do you have a field in date format?

if you have it try something like this:

Sum ({$<TP_DATE={"(>=$(=date(yearstart(addyears(Max(TP_DATE),-1)))) <=$(=date(addyears(Max(TP_DATE),-1))) "}>} TP_GROSS)

not tested maybe some ")" missing

Re: Date format

krishna20 — Mon, 21 Jul 2014 09:43:16 GMT

Hi Satya,

Even i changed in the script ,i'm getting the same result.

what may be the problem?

Regards

Krishna

Re: Date format

Anonymous — Mon, 21 Jul 2014 09:48:50 GMT

Hi,

Share some sample data with app for quick resolution.

Re: Re: Date format

krishna20 — Mon, 21 Jul 2014 09:57:51 GMT

Hi Jayadev,

i have attached the file here.which not modified.Can u pls tell whether the LYTD which i done is right ..

Regards

krishna

Re: Date format

Anonymous — Mon, 21 Jul 2014 10:20:01 GMT

Hi Kalyan,

I quickly checked your logic for LYTD Premium and it seems to be fine. Even the result you are getting is correct. As per below, if currency=3 and premium=2 than sum of previous year TP_NetPremium_LC_3 which is 83132.15. And this is what you are getting in your dashboard.

Re: Re: Date format

krishna20 — Mon, 21 Jul 2014 10:34:29 GMT

Hi Satya,

I'm getting previous year figure is right.But i need to show the data for previous year for same month.i.e; july.

can u please modify it.

Regards

krishna

Re: Re: Date format

Mon, 21 Jul 2014 10:37:15 GMT

It's not correct, you are not doing a LYTD you're filtering the whole last year

I should use TP_APPR_DT field is a date but un number format:

LYTD formula:

=if(

vOptionCurrency = 1 and vOptionPremium = 1, Sum({<TP_APPR_DT={">=$(=num(yearstart(addyears(Max(TP_APPR_DT),-1)))) <=$(=num(addyears(Max(TP_APPR_DT),-1))) "}>}TP_NetPremium_LC_1),

if(

vOptionCurrency = 1 and vOptionPremium = 2, Sum({<TP_APPR_DT={">=$(=num(yearstart(addyears(Max(TP_APPR_DT),-1)))) <=$(=num(addyears(Max(TP_APPR_DT),-1))) "}>}TP_GrossPremium_LC_1),

if(

vOptionCurrency = 3 and vOptionPremium = 1, Sum({<TP_APPR_DT={">=$(=num(yearstart(addyears(Max(TP_APPR_DT),-1)))) <=$(=num(addyears(Max(TP_APPR_DT),-1))) "}>}TP_NetPremium_LC_3),

if(

vOptionCurrency = 3 and vOptionPremium = 2, Sum({<TP_APPR_DT={">=$(=num(yearstart(addyears(Max(TP_APPR_DT),-1)))) <=$(=num(addyears(Max(TP_APPR_DT),-1))) "}>}TP_GrossPremium_LC_3)))))

Re: Re: Date format

krishna20 — Mon, 21 Jul 2014 10:47:03 GMT

Hi David,

But u didn't compared the month date here.sorry,i'm new this concept.

Regards,

Krishna