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: 
Luke_Killer_IT
Creator
Creator

how to show 6 months

hello,

I have a problem because I would like to see the prices of 6 months as you can see in the screenshot I have only one price from this year missing 5 from the previous year 

this is the script I use to display prices 

Max({<gueltig_ab={">$(=AddMonths(Max(gueltig_ab),-6))"}>}Preis)

Luke_Killer_IT_0-1659606267217.png

 

Labels (4)
1 Solution

Accepted Solutions
marcus_sommer

Could it be that you don't want to show the last 6 month else the last 6 price-entries? In this case you may try to use max(Date, 6) to reference to the appropriate date in the past.

But more likely is probably that you want respectively need (you didn't ask directly in this direction) a general resolution of the price-table against your fact-data and with it comes then a connection to the period-fields from a master-calendar. To get something like this you will need to do several additionally things:

  • creating a new Gueltig_bis field which contained the value from the previous Gueltig_ab value unless for the most current ones which may get today()
  • based on the above you could apply some intervalmatch-logic with it against a date to get a price-value for each single date
  • this price-information could then be joined/mapped against the facts and maybe in further steps multiplied with amount/tax/discount information and/or making differences to any other available price information

This means that now for each date and therefore derived to any needed period-field are directly prices available and you may now apply min/max/avg and/or a count(distinct) and/or a weighted averages logic to show the development of the prices over the time.

Of course it's not a matter of a few minutes else it may require a complete re-design of the data-model + UI from your application but in the long term it would very powerful within the analysis. 

- Marcus

View solution in original post

10 Replies
sidhiq91
Specialist II
Specialist II

@Luke_Killer_IT  Not sure about you requirement though, as per your requirement, it will max price of the last 6 months.

Could you please provide some sample data?

Luke_Killer_IT
Creator
Creator
Author

@sidhiq91 

if it helps you I can give you the next dates with the prices that should occur at most 6 months back

Luke_Killer_IT_0-1659610403025.png

 

sidhiq91
Specialist II
Specialist II

@Luke_Killer_IT  Yes please provide in the excel format. Just a sample. I will help you out.

Luke_Killer_IT
Creator
Creator
Author

@sidhiq91 please this is the data I use

sidhiq91
Specialist II
Specialist II

@Luke_Killer_IT If I understand correctly you need to find the Max of price in the last 6 months right?

Max({<Gueltig_ab={">=$(=Addmonths(Max(Gueltig_ab),-6))"}>}Preis)

Backend:

Temp:
LOAD
Teile,
TeileArt,
LagerMe,
Bezeichnung1,
Date(Gueltig_ab,'MM/DD/YYYY') as Gueltig_ab,
Num(left(Preis,5)) as Preis,
ME,
TeileGruppe
FROM [lib://Qlik Community Practice/Last 6 Months.xlsx]
(ooxml, embedded labels, table is Sheet1);

Exit Script;

Luke_Killer_IT
Creator
Creator
Author

@sidhiq91 yes,you understand

Luke_Killer_IT
Creator
Creator
Author

@sidhiq91 and have you come up with any ideas because I completely don't know how to do it anymore 😞

sidhiq91
Specialist II
Specialist II

@Luke_Killer_IT  I have already given you the expression which will give you the Max value from the last 6 months. If that is not what you are looking for then you can give me the expected output and i will try to help you out.

Luke_Killer_IT
Creator
Creator
Author

@sidhiq91 

should end with a maximum of 6 months from the maximum date and then displays all months

Luke_Killer_IT_0-1659691353522.png

Luke_Killer_IT_1-1659691522435.png