Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data Preparation in Load

Hi All, I have a complicated data set and I am having a hard time creating a straight table to show YTD vs Prior Year for the same months*. If anybody can assist please provide your input. If you can provide a workbook that would be much appreciate it.

This is the current stay of the data:  

Date   Health    401K Training
1/1/201415103
1/2/2015123154
2/6/201515135
3/1/201520515
4/1/2015633232
5/1/2015152612
6/1/2015264615
1/6/2016323231
2/9/2016151412

This is the straight table I want to create:  

Fund NameYTD as Feb 2016YTD as Feb 2015Variance
Health47138-91
401K4664-18
Training43934

*Note that there might be different dates in the same month. I want to compare the months irrespective of the dates.

6 Replies
Anonymous
Not applicable
Author

Hi ,

Can you say which aggregation function you used to calculate in your straight table output.

sunny_talwar

You want something like this?

Capture.PNG

I used a crosstable to transpose your data, not sure if you already have it like this or if you have different columns for each FundName.

See if the attached is helpful.

Script:

Table:

CrossTable(FundName, Value, 3)

LOAD Date,

  MonthName(Date) as MonthYear,

  Month(Date) as Month,

  Health,

  [401K],

  Training

INLINE [

    Date,    Health,     401K, Training

    1/1/2014, 15, 10, 3

    1/2/2015, 123, 15, 4

    2/6/2015, 15, 13, 5

    3/1/2015, 20, 51, 5

    4/1/2015, 63, 32, 32

    5/1/2015, 15, 26, 12

    6/1/2015, 26, 46, 15

    1/6/2016, 32, 32, 31

    2/9/2016, 15, 14, 12

];

Dimension:

FundName

Expressions

1) =Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}, MonthYear, Month>}Value)

2) =Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -12)))"}, MonthYear, Month>}Value)

3) =Column(1) - Column(2)

Best,

Sunny

mightyqlikers
Creator III
Creator III

hi,

Find the attached.

$@M.

Anonymous
Not applicable
Author

Hi ,

Please find this Attachment . Maybe it match your requirement .

Not applicable
Author

Thanks. You guys rock!

oknotsen
Master III
Master III

If your question is now answered, please flag the Correct Answer and possible Helpful answers.

If not, please make clear with which part of this topic you still like to get help .

May you live in interesting times!