Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
dseelam
Creator II
Creator II

Current month price to whole year

Hello All,

How can I apply current month selling Price for all in months across year?- Pivot table 

In below example 6.8 is the price in January-2020 and from mid of jan price changed from 6.8 to 6.9 going forward months, not I want to apply 6.9 which is current month price across all months in 2020 which I should be only one line with selling price 6.9

total amount is units * selling price  

 Capture1.PNG

 

 

 

Thanks,

Dinesh 

Labels (3)
1 Solution

Accepted Solutions
dseelam
Creator II
Creator II
Author

Hello Brett,

 

Sorry for delayed Vegar's response did not work out for this requirement posted

I used below solution to achieve the requirement 

tmp:
LOAD Distinct
Key,
date(Min(Fo_Year_Month),'MMM-YYYY') as YM
Resident Demantra
Where Fo_Year_Month >= date(MonthStart(Now()),'MMM-YYYY')
Group by Key;

Left join (tmp)
LOAD Key,
Fo_Year_Month as YM,
[Selling Price] as SP_NEW
Resident Dem;

View solution in original post

8 Replies
Vegar
Partner
Partner

Try an expression similar to this: 

=$(=only(total {<Date = {"$(=maxstring(Date))"}>}aggr(only(Price), Date))) * Sum(Units)

image.png

 

See attached QVW file

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
dseelam
Creator II
Creator II
Author

Thank you Vegar,

But I don't have any date column, all I have is Year, Month & Years. to use in expression

Vegar
Partner
Partner

I see. Lets try to solve it anyhow.

How do you identify which price is to be considered the latest price in your data set? You mention that you have two prices during the month of January?  Lets say you have a change of prices yesterday (April 12). How can you know which april price is the latest? 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
dseelam
Creator II
Creator II
Author

Hi Vegar,

You are right my bad , So I want to apply every month next month selling price across all years  since its forecast next month selling price will always have only one selling price.

Vegar
Partner
Partner

My question is still relevant. If you change prices mid april. How can you identify which price that should apply on the Mars transactions? 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Vegar
Partner
Partner

By assuming you have a transaction ID to identify which is the latest transaction price you could do as I've done in my attached example.

Vegar_0-1586847198706.png

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Brett_Bleess
Support (Former)
Support (Former)

Did Vegar's last post/example get you what you needed?  If so, we would appreciate it greatly if you would return to your thread and use the Accept as Solution button on that post to give him credit for the assistance and let other Members know that did work for you.  If you did something different, consider posting that and then use the button on that post to mark it.  

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
dseelam
Creator II
Creator II
Author

Hello Brett,

 

Sorry for delayed Vegar's response did not work out for this requirement posted

I used below solution to achieve the requirement 

tmp:
LOAD Distinct
Key,
date(Min(Fo_Year_Month),'MMM-YYYY') as YM
Resident Demantra
Where Fo_Year_Month >= date(MonthStart(Now()),'MMM-YYYY')
Group by Key;

Left join (tmp)
LOAD Key,
Fo_Year_Month as YM,
[Selling Price] as SP_NEW
Resident Dem;

View solution in original post