Solved: Re: Date format - Qlik Community

krishna20 · ‎2014-07-21

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

Report Inappropriate Content · ‎2014-07-21

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)))))

View solution in original post

alexandros17 · ‎2014-07-21

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

MK_QSL · ‎2014-07-21

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

Report Inappropriate Content · ‎2014-07-21

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

Thanks,

TDP

Report Inappropriate Content · ‎2014-07-21

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';

krishna20 · ‎2014-07-21

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

alexandros17 · ‎2014-07-21

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

Anonymous · ‎2014-07-21

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)

krishna20 · ‎2014-07-21

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

krishna20 · ‎2014-07-21

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