topic Re: Data Problem in QlikView

Data Problem

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

Hi at all,

I have found this issue:

I have two data set: one for revenues and other one for costs, in each one I have the invoice data, payment data.

I would like to create a table with Months on column and for each months I would like to have one row for revenues and other one for costs.

The problem is that i would like to create an input box with the possibility to select or Month coming from Invoice Data or coming from Payment Data.

Example:

Revenue Table

N° Invoice, Invoice Data,Payment Data, Amount

1, 01/01/2017, 01/02/2017, 30

2, 01/02/2017, 01/03/2017, 40

3, 01/03/2017, 20/03/2017, 10

Cost Table

N° Invoice, Invoice Data,Payment Data, Amount

1, 01/01/2017, 01/02/2017, 15

2, 01/02/2017, 01/03/2017, 20

3, 01/03/2017, 20/03/2017, 20

Then if i select Invoice from Input box i would like to have a result like this:

Variable	Gen	Feb	Mar
Revenue	30	40	10
Cost	15	20	20
Profit	15	20	-10

If i select Payment from Input box:

Variable	Feb	Mar
Revenue	30	50
Cost	15	40
Profit	15	10

Is it possible to do this?

Thank in advance

Andrea

Re: Data Problem

MarcoWedel — Tue, 01 Aug 2017 23:05:59 GMT

Hi,

one solution might be:

tabCostRevenue:
LOAD *,
    'Revenue' as Variable,
    RowNo() as ID
INLINE [
    N° Invoice, Invoice Date, Payment Date, Amount
    1, 01/01/2017, 01/02/2017, 30
    2, 01/02/2017, 01/03/2017, 40
    3, 01/03/2017, 20/03/2017, 10
];

LOAD *,
    'Cost' as Variable,
    RowNo() as ID
INLINE [
    N° Invoice, Invoice Date, Payment Date, Amount
    1, 01/01/2017, 01/02/2017, 15
    2, 01/02/2017, 01/03/2017, 20
    3, 01/03/2017, 20/03/2017, 20
];

tabDateLink:
CrossTable (DateTypeTemp, Date)
LOAD ID,
    [Invoice Date],
    [Payment Date]
Resident tabCostRevenue;
Join
LOAD Distinct
    DateTypeTemp,
    SubField(DateTypeTemp,' ',1) as DateType
Resident tabDateLink;

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Date) as MinDate, 
    Max(Date) as MaxDate 
Resident tabDateLink;

hope this helps

regards

Marco

Re: Data Problem

andrea90casa — Wed, 02 Aug 2017 21:57:14 GMT

Hi Marco,

Thanks for your help, you gave to me a great idea about how to solve my issue.

I have one more question,

The table for costs and revenues are more complicated than those I've shown you by example.

costs analysis for example use three tables:

1) Supplier Data

2) Product Data

3) Costs Table

The revenues analysis is the same but the columns inside are not equal.

I see that when you load your script the revenues data and costs data create a new table with a variable that indicates if the record is a cost or a revenue.

My question is: Is it possible do the same thing even if the source table (for revenue and costs) are not equal or it is necessary that they have the same columns?

Best Regards

Andrea

Re: Data Problem

MarcoWedel — Wed, 02 Aug 2017 22:01:19 GMT

Hi,

this should be possible.

Can you please share some sample lines of each table and your expected result?

thanks

Marco

Re: Data Problem

andrea90casa — Fri, 04 Aug 2017 12:48:01 GMT

Hi Marco, !

Sorry for the delay,

I attached an xlsx file with I tried to insert some example table (three for costs and other three for revenues):

My goal is to upload this tables on a qlik view document and do some visualization like revenue by customers, by area, analyse products purchased, etc etc

But also i would like to create a table or other visualization in which I compare revenue and costs by months (there are three types of data), months could be by invoice data (sales and costs), payment data or competence data.

I tried your previous solution and by the costs everything is ok (even if I am trying to create a calculated field that allows me to compare costs of current months compared with previous month and it is not so easy and i have to try again )

Thanks again for your help

Andrea

Re: Data Problem

andrea90casa — Fri, 04 Aug 2017 21:43:40 GMT

Hi Manuel,

I would like to ask you if you can help me to create a formula, for cost data, that allow me to calc the difference between the amount of current month and amount of previous.

So i would like to create a bar chart with months on X axis and in the tooltip the information about the % difference between the month selected and the previous one.

I tried to create an ID for each month (because i would like to make a comparison even if I select January because i want to compare that amount with the amount of December but previous year)

I called this field Month_ID and now i would like to know if you have some suggestion for it.

Andrea