Solved: Re: Count - Qlik Community

Report Inappropriate Content · ‎2016-01-12

Hi All

can any one help me on the following scenario, I have 3 fields Design Date, Sale Date Item , Sale, by using these three fields I would like to calculate Total Months count for each Item from Design date and cumulative sum of Sale for each item to Sale date.

Thanks

John

sunny_talwar · ‎2016-01-12

Unable to get the results in months, but for days may be this:

Table:

LOAD [Design Date],

[Sale Date],

Item,

Sale

FROM

[Testdata (3).xlsx]

(ooxml, embedded labels, table is Sheet1);

Concatenate(Table)

LOAD Only(Item) as Item,

Date(Today()) as [Design Date],

Date(Today()) as [Sale Date],

0 as Sale

Resident Table

Group By Item;

Join(Table)

LOAD Min([Sale Date]) as [Min Sale Date],

Item

Resident Table

Group By Item;

FinalTable:

LOAD *,

[Sale Date] - [Min Sale Date] as DaysDifference,

If(Item = Peek('Item'), RangeSum(Peek('Cumulative Mileage'), Sale), Sale) as [Cumulative Mileage]

Resident Table

Order By Item, [Sale Date];

DROP Table Table;

View solution in original post

sunny_talwar · ‎2016-01-12

Can you update the excel with your desired output to understand what exactly you are looking for?

Report Inappropriate Content · ‎2016-01-12

Sure

miguelbraga · ‎2016-01-12

Here you go. Check the qvw attached:

Good Job

miguelbraga · ‎2016-01-12

I assume this is what your looking for

Report Inappropriate Content · ‎2016-01-12

Updated

PradeepReddy · ‎2016-01-12

1. total months for each item, Monthdiff(DesignDate,SaleDate)

2. For cumulative sum, please share the sample output, what you are looking for..?

Report Inappropriate Content · ‎2016-01-12

Count is Correct But Sale is not tied , need to work on this , thanks.

miguelbraga · ‎2016-01-12

I'll see what I can do

miguelbraga · ‎2016-01-12

Maybe Sales is:

= max(Aggr(sum(Sale),Item))

Or this:

= aggr( NODISTINCT sum(Sale), Item )

You choose

Count

Related Topics