Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count

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

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

18 Replies
sunny_talwar

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

Not applicable
Author

Sure

miguelbraga
Specialist III
Specialist III

Here you go. Check the qvw attached:

Good Job

miguelbraga
Specialist III
Specialist III

I assume this is what your looking for

Not applicable
Author

Updated

PradeepReddy
Specialist II
Specialist II

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

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

Not applicable
Author

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

miguelbraga
Specialist III
Specialist III

I'll see what I can do

miguelbraga
Specialist III
Specialist III

Maybe Sales is:

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

Or this:

= aggr( NODISTINCT sum(Sale), Item )

You choose